CODEWITHSUNDEEP
sqldatabasesql-server-2005selection
Christian
Christian

Reputation: 1

SQL - Select by status

I need to do the following selection.

I have a list of 100.000 user with different status (1 - 4). I need to choose an amount of 10.000 user out of this, first all with status 4 (but if there are less than 10.000 with status 4 than choose all with status 3 and than with 2 and than with 1)

Help is highly appreciated.

Thanks for replies, I tried to go with Gordons Version. As I need to union I have the following now. But this allows me only to prioritize the score for the second selection (%@test2.com) but I would need it for every selection that I create. If I put the "order by" before the union I receive an invalid syntax notification:

SELECT TOP 10000 *
FROM [table]
WHERE Email like '%@test1.com' and score in ( 1, 2, 3, 4)
UNION
SELECT TOP 5000 *
FROM [table]
WHERE Email like '%@test2.com' and score in ( 1, 2, 3, 4)
order by score desc

Upvotes: 0

Views: 3131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

This is a prioritization query. You can handle it using the ANSI standard function row_number():

select t.*
from (select t.*,
             row_number() over (order by status desc) as seqnum
      from t
      where status in (1, 2, 3, 4)
     ) t
where seqnum <= 10000;

You can also simplify this to:

select t.*
from t
where status in (1, 2, 3, 4)
order by status desc
fetch first 10000 rows only;

Upvotes: 2

Related Questions