How to choose the top x from with options?

Question

I am using Sql-Server 2005

I have a user table with user id and gender. I want to select the top 1000 men (0) and the top 1000 women (1) at userID desc.

If I create a union, only one result set is ordered by userID. What other way to do it?

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

Answer 1

Another way to do 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

Answer 2

Martin Smith's decision 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 if you prefer the last user first, but you get the top 1000 of them.

Answer 3

, . order by , SQL Server :

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

, . , ! :

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

, order by :

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

, - id. . order by :

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

, , . , , union order by , union .

Chris Diver, - order by :

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

Sample 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 4

You need to make sure that you create a subselect to combine, and then execute orders outside 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

In addition, using UNION ALL will give better performance, since db will not check for duplicates (which will not be in this query) in the results.