Top 2 with Count

Question

Top 2 with Count

niladri.primalink

SSCommitted

Points: 1751
More actions
June 28, 2013 at 5:17 am

#295746

Hi All,
I have written a query.
declare @N int
declare @N int
Set @n=isnull(@N,2000)
select top(@N) * from
(
select 10 'Top 2'
Union
Select 20
Union
Select 30
union
select 40
union
select 50
)T1
this is working fine. if change 2000 to 2 it gives me the result only 2 results. Now my question is when I am not setting any value then it should show me all the records.
declare @N int
--Set @n=isnull(@N,2000)
select top(@N) * from
(
select 10 'Top 2'
Union
Select 20
Union
Select 30
union
select 40
union
select 50
)T1
when I am executing the above query it gives the error that TOP clause contains an invalid value as I have not set any value.
I want to see all the records when I am not setting any value.
Please help..........
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

A NULL value is not allowed in the TOP function, hence the error. Change the number 2000 in the first script to a high number equal to or higher than the rowcount. When @N is not set (equals NULL) it will be replaced by the high number and will give you the complete result.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

niladri.primalink SSCommitted Points: 1751 More actions · Answer 2

niladri.primalink

SSCommitted

Points: 1751

June 28, 2013 at 5:48 am

#1628260

thanks...got it

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Nilandri, can you please post what changes you made in your query so that other s can also learn from that thing...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

I think this will work for you....

declare @N int=2

If ISNULL(@N,0) = 0

select * from

(

select 10 'Top 2'

Union

Select 20

Union

Select 30

union

select 40

union

select 50

)T1

ELSE

select top(@N) * from

(

select 10 'Top 2'

Union

Select 20

Union

Select 30

union

select 40

union

select 50

)T1

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

A couple of thoughts. First it might be a lot easier to do this by adding ROW_NUMBER to your query, then you can just a Where RowNum < @N

Secondly, and far more important is that in your query you are selecting top but there is no order by. With no order by it is like saying, "give me the first N rows that the engine thinks is the easiest to find".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

niladri.primalink SSCommitted Points: 1751 More actions · Answer 6

hi All,

Thanks for your reply. it is now working fine

@kapil: your code has helped a lot. Thanks buddy.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

niladri.primalink (6/30/2013)
hi All,
Thanks for your reply. it is now working fine
@kapil: your code has helped a lot. Thanks buddy.

thanks Niladri, but Sean is right as he said using TOP without ORDER BY is risky and might you will not get your desired output

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/