Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get 500 records without using TOP

Author  Topic 

Petronas
Posting Yak Master

134 Posts

Posted - 2013-03-14 : 15:28:32
Hi All,

I have to get 500 customer ids from order date >=1/1/2008 to order date <1/1/2013. Currently in my code I am using top 500 but that is pulling only customer ids from 2008 and 2009. Is there a way that I can get a broader range of customers spread out from 2008 to 2012?

Thanks,
Petronas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 15:35:53
maybe your select is fine and you can add to the end: ORDER BY NEWID() to shuffle the records
but maybe it is slowing down the performance...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-14 : 15:39:36
Are you saying that you want to limit the number you get at one time to 500 but the criteria would return many more? One way would be to include an ORDER BY clause. If you want the most recent then you can order by orderDate descending. If you really need some sort of even distribution across that time range then you need to define what the rules should be. Perhaps you can get lucky by ordering by something unrelated to date like customerName.

EDIT:
the more obvious answer: Remove the "top 500". that will give you your full distribution for your daterange.

Be One with the Optimizer
TG

Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

134 Posts

Posted - 2013-03-19 : 09:27:38
Thank you so much for the replies. I am out sick and could not get back. I used both the suggestions and it is working but not what the client expects :-) Thanks again
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-19 : 11:05:00
continued here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183898


Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -