select query

Dear Experts,
I have two requirements here with my select query ( see attach code)
I wanting to have 2.5 % of records. I want random records and not the TOP records. how could i get this?

Second requirement. if you see my where clause i have disposition in (RNR,ptp,nb,cb) now I want this in priority.
it should check if it got RNR records if not  then it should check for PTP and then NB .. so on.
how could I set the priorty search?
Any suggestions are highly appreciated.
 
select top 2.5 percent * from [table_name]
where status='negative' and deli_day='30' and 
[disposition] in ('RNR','PTP','NB','CB')

Open in new window

parpaaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to use the TABLESAMPLE syntax:
http://www.mssqltips.com/tip.asp?tip=1308
0
 
JermTheWormCommented:
select top 2.5 percent * from [table_name]
 where status='negative' and deli_day='30' and
 [disposition] in ('RNT','PTP','NB','CB')
order by
 disposition desc,
  rand()*10000

This will work because 'RNT','PTP','NB','CB' happen to be in reverse alphabetical order.
0
 
parpaaAuthor Commented:
Thanks @Jerm,
what if it is not in the reverse alphabetical order

say something like this?
where in (PTP,ABC,RNR,NB)
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
parpaaAuthor Commented:
Thanks @Angel
Tablesample clause is something new to me. thanks for sharing
0
 
Anthony PerkinsCommented:
Don't use RAND() in a SQL Statement it is not random, instead use NEWID().
0
 
JermTheWormCommented:
then you'll have to do a CASE clause to get them in the order you want i.e.
... order by
(CASE [disposition]
   when 'PTP' THEN 1
   when 'ABC' THEN 2
 .... etc ....
END)

p.s. I didn't test that but it should work... I think.
0
 
parpaaAuthor Commented:
Thank you soo much for your time guys .. It was good learning.
@Jerm I will test that. I understand the logic behind, thats all I want.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.