How to select top x from with params?

Question

I'm using Sql-Server 2005

I have Users table with userID and gender. I want to select top 1000 males(0) and top 1000 females(1) order by userID desc.

If i create union only one result set is ordered by userID desc. What other way to do that?

SELECT top 1000 *
FROM Users
where gender=0
union
SELECT top 1000 *
FROM Users
where gender=1
order by userID desc

Answer 1

You need to ensure that you create a sub-select for the union, then do the ordering outside over the combined results.

Something like this should work:

SELECT u.*
  FROM (SELECT u1a.* FROM (SELECT TOP 1000 u1.*
                             FROM USERS u1
                            WHERE u1.gender = 0
                            ORDER BY u1.userid DESC) u1a
        UNION ALL
        SELECT u2a.* FROM (SELECT TOP 1000 u2.*
                             FROM USERS u2
                            WHERE u2.gender = 1
                            ORDER BY u2.userid DESC) u2a
       ) u
ORDER BY u.userid DESC

Also, using a UNION ALL will give better performance as the db won't bother checking for duplicates (which there won't be in this query) in the results.

Answer 2

Done some testing, and the results are pretty strange. If you specify an order by in both parts of a union, SQL Server gives a syntax error:

select top 2 * from @users where gender = 0 order by id
union all
select top 2 * from @users where gender = 1 order by id 

That makes sense, because the order by should only be at the end of the union. But if you use the same construct in a subquery, it compiles! And works as expected:

select * from (
    select top 2 * from @users where gender = 0 order by id
    union all
    select top 2 * from @users where gender = 1 order by id
) sub

The strangest thing happens when you specify only one order by for the subquery union:

select * from (
    select top 2 * from @users where gender = 0
    union all
    select top 2 * from @users where gender = 1 order by id
) sub

Now it orders the first half of the union at random, but the second half by id. That's pretty unexpected. The same thing happens with the order by in the first half:

select * from (
    select top 2 * from @users where gender = 0 order by id desc
    union all
    select top 2 * from @users where gender = 1
) sub

I'd expect this to give a syntax error, but instead it orders the first half of the union. So it looks like union interacts with order by in a different way when the union is part of a subquery.

Like Chris Diver originally posted, a good way to get out of the confusion is not to rely on the order by in a union, and specify everything explicitly:

select  *
from    (
        select  *
        from    (
                select  top 2 *
                from    @users
                where   gender = 0
                order by 
                        id desc
                ) males
        union all
        select  *
        from    (
                select  top 2 *
                from    @users
                where   gender = 1
                order by 
                        id desc
                ) females
        ) males_and_females
order by 
        id

Example data:

declare @users table (id int identity, name varchar(50), gender bit)

insert into @users (name, gender)
          select 'Joe', 0
union all select 'Alex', 0
union all select 'Fred', 0
union all select 'Catherine', 1
union all select 'Diana', 1
union all select 'Esther', 1

Answer 3

Martin Smith's solution is better than the following.

SELECT UserID, Gender
FROM 
  (SELECT TOP 1000 UserId, Gender 
   FROM Users 
   WHERE gender = 0
   ORDER BY UserId DESC) m
UNION ALL
SELECT UserID, Gender
FROM 
 (SELECT TOP 1000 UserId, Gender
  FROM Users
  WHERE gender = 1
  ORDER BY UserId DESC) f
ORDER BY Gender, UserID DESC

This does what you want, just change the order by if you'd rather have the latest user first, but it will get you the top 1000 of each.

Answer 4

Another way of doing it

WITH TopUsers AS
(
SELECT UserId, 
       Gender,
       ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY UserId DESC) AS RN
  FROM Users
  WHERE Gender IN (0,1) /*I guess this line might well not be needed*/
) 

SELECT UserId, Gender 
FROM TopUsers  
WHERE RN <= 1000
ORDER BY UserId DESC