x

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!

more ▼

asked Apr 13 at 01:37 AM in Default

avatar image

red68
498 11 16 25

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)

more ▼

answered Apr 13 at 07:12 AM

avatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208

asked: Apr 13 at 01:37 AM

Seen: 32 times

Last Updated: Apr 13 at 06:42 PM

Copyright 2018 Redgate Software. Privacy Policy