asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

JermTheWorm

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.

parpaa

ASKER

Thanks @Jerm,
what if it is not in the reverse alphabetical order

say something like this?
where in (PTP,ABC,RNR,NB)

parpaa

ASKER

Thanks @Angel
Tablesample clause is something new to me. thanks for sharing