Select Query to select all available records
Goal is to select 10,000 records using the Priority_Flag by selecting records until quantity is met of 10,000 in the order of the Priority_Flag. So, select all records where Priority_Flag = 1A, then all for 2A which totals 5000. I only need 5000 more to meet my goal of 10000. So, last only select 5000 of 3A. Then stop process. Probably, something like enter 10000 for quantity needed. Then go check this Priority_Flag starting with 1A, 2A, 3A, 4A until desired quantity is met. 1A contains 2500 records; 2A contains 2500 records; 3A contains 10000 records; 4A contains 5000 records. Thanks!
You could order your data according to the priority flag and then select the top 10000:
select top 10000
col1, col2, etc......
from YourTable
where
..any filters....
order by
case
when Priority_Flag = '1A' then 1
when Priority_Flag = '2A' then 2
when Priority_Flag = '3A' then 3
when Priority_Flag = '4A' then 4
end
This way you are telling SQL *declaratively* what you need, rather than how to get the results (*procedurally*)
I would add an `ELSE` to that `CASE` expression, though...
case...
when...
else 999 -- any number that's bigger than the other numbers already given
end
@ThomasRushton, indeed - thank you for picking me up on that!
