Mfcttrf

Improving software when the author can see no need for improvement

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

Is The Lion King live action film made in motion capture?

Use of "When" in present vs "whenever"

What is a "Genuine Geraldo interviewee"?

Colleagues speaking another language and it impacts work

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

Why can I log in to my Facebook account with a misspelled email/password?

Is multiplication of real numbers uniquely defined as being distributive over addition?

Physics of Guitar frets and sound

SQL Minimum Row count

Why does Intel's Haswell chip allow multiplication to be twice as fast as addition?

Yajilin minicubes: the Hullabaloo, the Brouhaha, the Bangarang

Can I call myself an assistant professor without a PhD

Generator for parity?

How do we avoid CI-driven development...?

Replace data between quotes in a file

How would I as a DM create a smart phone-like spell/device my players could use?

Circle around all points of a layer

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

Does two puncture wounds mean venomous snake?

Best gun to modify into a monsterhunter weapon?

Tikzcd pullback square issue

Can a character who casts Shapechange and turns into a spellcaster use innate spellcasting to cast spells with a long casting time?

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 8 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 8 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 8 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 8 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 8 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 8 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 8 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 8 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
  8 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♦
  8 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 8 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
  8 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♦
  8 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 8 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
  8 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♦
  8 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 8 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 8 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

answered 8 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges

answered 8 hours ago

nateirvinnateirvin

54544 silver badges1717 bronze badges