Select Query to select all available records

Question

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!

Answer 1

0

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)

answered Apr 13 at 07:12 AM

Kev Riley ♦♦
66.8k ● 48 ● 65 ● 81

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

Apr 13 at 08:09 AM ThomasRushton ♦♦

@ThomasRushton, indeed - thank you for picking me up on that!

Apr 13 at 02:38 PM Kev Riley ♦♦

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Select Query to select all available records

Follow this question

Related Questions