I have the following columns in SQL Server 2008 table t_list:
firstid, firstpoints, secondid, secondpoints

I want to select all of those columns in an SQL query but order in a specific way.

I want to select the top 400 of those columns, ordering by firstpoints only for the firstid column, and secondpoints only for the secondid column in one query, for example:

SELECT TOP 400 firstid, firstpoints 
FROM t_list 
ORDER BY firstpoints desc 

and

select top 400 secondid, secondpoints 
FROM t_list 
ORDER BY secondpoints desc

How can I fit the two above queries into one query, returning output like

firstid, firstpoints, secondid, secondpoints

1. firstidresult, firstpointresult, secondidresult, secondpointsresult
2. etc...

• sql
• sql-server-2008

This works on MS SQL Server

Select t1.FirstID, t1.FirstPoints, t2.SecondID, t2.SecondPoints
From (Select Top 400 row_number() Over (Order By FirstPoints desc) As r, FirstID, FirstPoints From t_list) As t1
    Join (Select Top 400 row_number() Over (Order By SecondPoints desc) As r, SecondID, SecondPoints From t_list) as t2 on t1.r = t2.r

Hope I understood your problem correctly.

    SELECT *
FROM (
    SELECT TOP 400 FirstId
        ,FirstPoints
    FROM t_list
    ORDER BY FirstPoints
    ) f

UNION

SELECT *
FROM (
    SELECT TOP 400 SecondId
        ,SecondPoints
    FROM t_list
    ORDER BY SecondPoints
    ) s

WITH CTE1 AS 
(SELECT 1 as [Order],
    firstid, 
    firstpoints,
    Row_Number() OVER (ORDER BY firstpoints desc ) as RowNum
FROM t_list 
),
CTE2 AS 
(SELECT 
    2,
    secondid, 
    secondpoints,
    Row_Number() OVER (ORDER BY secondpoints) as RowNum
FROM t_list 
)
SELECT [Order],
    firstid as Id, 
    firstpoints  as Points
FROM CTE1
WHERE RowNum <= 400
UNION 
SELECT [Order],
     secondid, 
     secondpoints 
FROM CTE2
WHERE RowNum <= 400
ORDER BY [Order], RowNum 

You just need to combine your two filters into one table using 'UNION ALL'

(SELECT TOP 400 firstid, firstpoints FROM t_list
ORDER BY firstpoints desc)

UNION ALL

(select top 400 secondid, secondpoints
FROM t_list
ORDER BY secondpoints desc )