Mdthbs

How to translate this word-play with the word "bargain" into French?

Is it possible to script what applications should open certain file extensions?

How many numbers in the matrix?

In the movie Harry Potter and the Order or the Phoenix, why didn't Mr. Filch succeed to open the Room of Requirement if it's what he needed?

Dereferencing a pointer in a for loop initializer creates a seg fault

Geometric programming: Why are the constraints defined to be less than/equal to 1?

Do other countries guarantee freedoms that the United States does not have?

Pretty heat maps

Is TA-ing worth the opportunity cost?

Acceptable to cut steak before searing?

Why did the RAAF procure the F/A-18 despite being purpose-built for carriers?

When "he's gone" means "he's dead", is it a contraction of "he is" or "he has"?

Is it really ~648.69 km/s delta-v to "land" on the surface of the Sun?

What is the idiomatic way of saying “he is ticklish under armpits”?

Why is there a need to prevent a racist, sexist, or otherwise bigoted vendor from discriminating who they sell to?

How do I calculate the difference in lens reach between a superzoom compact and a DSLR zoom lens?

How to identify the wires on the dimmer to convert it to Conventional on/off switch

Is refreshing multiple times a test case for web applications?

English - Acceptable use of parentheses in an author's name

Secure my password from unsafe servers

Plausibility of Ice Eaters in the Arctic

Yajilin minicubes: the Hullabaloo, the Brouhaha, the Bangarang

Are any jet engines used in combat aircraft water cooled?

Should I self-publish my novella on Amazon or try my luck getting publishers?

SQL Minimum Row count

Passing array parameters to a stored procedureHow to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?Stored Procedure doesn't show result in SQL ServerSaving a variable globally in SQL server it self and use it when requiredHow to pass multi-valued characters in SSRS Report?Advice on how to improve the efficiency of a stored procedure using lots of joinswhy an update doesn't update a row if the where clause exists?From a user table check if one of them exists on a tableDB Index MaintenanceShort circuit count query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

3

I have a stored procedure that allows users to pass in parameters.

SELECT * 

FROM Table1

WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.

sql-server

share|improve this question

asked 9 hours ago

referscusreferscus

11844 bronze badges

New contributor

referscus is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

3

I have a stored procedure that allows users to pass in parameters.

SELECT * 

FROM Table1

WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.

sql-server

share|improve this question

asked 9 hours ago

referscusreferscus

11844 bronze badges

New contributor

referscus is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I have a stored procedure that allows users to pass in parameters.

SELECT * 

FROM Table1

WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.

sql-server

share|improve this question

asked 9 hours ago

referscusreferscus

11844 bronze badges

New contributor

referscus is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

SELECT * 

FROM Table1

WHERE columnA =@paramA, ColumnB=@paramB....

share|improve this question

asked 9 hours ago

referscusreferscus

11844 bronze badges

New contributor

referscus is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 9 hours ago

referscusreferscus

11844 bronze badges

New contributor

referscus is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 9 hours ago

referscusreferscus

11844 bronze badges

New contributor

referscus is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 9 hours ago

referscusreferscus

11844 bronze badges

2 Answers
2

active

oldest

votes

6

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *

into mytable

from (select 'X' as c1) x

cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)



select * from mytable



--change the 10 to 11 to see it not return

select * from

(

select *, COUNT(*) OVER() CT

from mytable

where c1 = 'X'

) sub

where CT > 10

share|improve this answer

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thanks, that helps a ton!
  
  – referscus
  7 hours ago
  

  
  
  
  

add a comment | 

0

If the output has some sort of logical order, or even some arbitrary order that fits in with your 10 rows requirement, you could use the ROW_NUMBER() window function, like so:

SELECT * 

FROM 

(

    SELECT *,

        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber

    FROM Table1

    WHERE columnA =@paramA AND ColumnB=@paramB

) AS subq

WHERE rownumber > 10

share|improve this answer

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  This would only return the rows with your arbitrary identity is > 10. I think the OP wants to return the entire result set, if it's at least 10 rows. Thus, if the result set is 11 rows, return all 11.
  
  – scsimon
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Maybe change the last WHERE to WHERE EXISTS (SELECT 1 FROM subq WHERE rownumber > 10)... not at a computer, you might need to change subq to be a CTE...
  
  – Aaron Bertrand♦
  9 hours ago
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

referscus is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f245001%2fsql-minimum-row-count%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

6

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *

into mytable

from (select 'X' as c1) x

cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)



select * from mytable



--change the 10 to 11 to see it not return

select * from

(

select *, COUNT(*) OVER() CT

from mytable

where c1 = 'X'

) sub

where CT > 10

share|improve this answer

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thanks, that helps a ton!
  
  – referscus
  7 hours ago
  

  
  
  
  

add a comment | 

6

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *

into mytable

from (select 'X' as c1) x

cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)



select * from mytable



--change the 10 to 11 to see it not return

select * from

(

select *, COUNT(*) OVER() CT

from mytable

where c1 = 'X'

) sub

where CT > 10

share|improve this answer

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thanks, that helps a ton!
  
  – referscus
  7 hours ago
  

  
  
  
  

add a comment | 

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *

into mytable

from (select 'X' as c1) x

cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)



select * from mytable



--change the 10 to 11 to see it not return

select * from

(

select *, COUNT(*) OVER() CT

from mytable

where c1 = 'X'

) sub

where CT > 10

share|improve this answer

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

select *

into mytable

from (select 'X' as c1) x

cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)



select * from mytable



--change the 10 to 11 to see it not return

select * from

(

select *, COUNT(*) OVER() CT

from mytable

where c1 = 'X'

) sub

where CT > 10

share|improve this answer

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

answered 8 hours ago

scsimonscsimon

1,68755 silver badges1717 bronze badges

0

If the output has some sort of logical order, or even some arbitrary order that fits in with your 10 rows requirement, you could use the ROW_NUMBER() window function, like so:

SELECT * 

FROM 

(

    SELECT *,

        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber

    FROM Table1

    WHERE columnA =@paramA AND ColumnB=@paramB

) AS subq

WHERE rownumber > 10

share|improve this answer

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  This would only return the rows with your arbitrary identity is > 10. I think the OP wants to return the entire result set, if it's at least 10 rows. Thus, if the result set is 11 rows, return all 11.
  
  – scsimon
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Maybe change the last WHERE to WHERE EXISTS (SELECT 1 FROM subq WHERE rownumber > 10)... not at a computer, you might need to change subq to be a CTE...
  
  – Aaron Bertrand♦
  9 hours ago
  
  
  

  
  
  
  

add a comment | 

0

If the output has some sort of logical order, or even some arbitrary order that fits in with your 10 rows requirement, you could use the ROW_NUMBER() window function, like so:

SELECT * 

FROM 

(

    SELECT *,

        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber

    FROM Table1

    WHERE columnA =@paramA AND ColumnB=@paramB

) AS subq

WHERE rownumber > 10

share|improve this answer

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  This would only return the rows with your arbitrary identity is > 10. I think the OP wants to return the entire result set, if it's at least 10 rows. Thus, if the result set is 11 rows, return all 11.
  
  – scsimon
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Maybe change the last WHERE to WHERE EXISTS (SELECT 1 FROM subq WHERE rownumber > 10)... not at a computer, you might need to change subq to be a CTE...
  
  – Aaron Bertrand♦
  9 hours ago
  
  
  

  
  
  
  

add a comment | 

If the output has some sort of logical order, or even some arbitrary order that fits in with your 10 rows requirement, you could use the ROW_NUMBER() window function, like so:

SELECT * 

FROM 

(

    SELECT *,

        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber

    FROM Table1

    WHERE columnA =@paramA AND ColumnB=@paramB

) AS subq

WHERE rownumber > 10

share|improve this answer

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

SELECT * 

FROM 

(

    SELECT *,

        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber

    FROM Table1

    WHERE columnA =@paramA AND ColumnB=@paramB

) AS subq

WHERE rownumber > 10

share|improve this answer

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

answered 9 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges