I have the following columns in SQL Server 2008 table
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
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...
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
ORDER BY firstpoints desc)
(select top 400 secondid, secondpoints
ORDER BY secondpoints desc )