CODEWITHSUNDEEP
sqlsql-server
Espen
Espen

Reputation: 3727

How to order an already ordered subquery

Creating this table:

CREATE TABLE #Test (id int, name char(10), list int, priority int)

INSERT INTO #Test VALUES (1, 'One', 1, 1)
INSERT INTO #Test VALUES (2, 'Two', 2, 1)
INSERT INTO #Test VALUES (3, 'Three', 3, 2)
INSERT INTO #Test VALUES (4, 'Four', 4, 1)
INSERT INTO #Test VALUES (5, 'THREE', 3, 1)

and ordering it by, list and priority:

SELECT * FROM #Test ORDER BY list, priority

 1 | One   | 1 | 1
 2 | Two   | 2 | 1
 5 | THREE | 3 | 1
 3 | Three | 3 | 2
 4 | Four  | 4 | 1

However I want to step through rows one by one selecting the top one for each list ordered by priority, and start over when I get to the end.

For example with this simpler table:

 1 | One   | 1 | 1
 2 | Two   | 2 | 1
 3 | Three | 3 | 1
 4 | Four  | 4 | 1

and this query:

SELECT TOP 1 * FROM #Test ORDER BY (CASE WHEN list>@PreviousList THEN 1 ELSE 2 END)

If @PreviousList is the list for the previous row I got, then this will select the next row and gracefully jump to the top when I have selected the last row.

But there are rows that will have the same list only ordered by priority - like my first example:

 1 | One   | 1 | 1
 2 | Two   | 2 | 1
 5 | THREE | 3 | 1
 3 | Three | 3 | 2
 4 | Four  | 4 | 1

Here id=3 should be skipped because id=5 have the same list ordering and a better priority. The only way I can think of doing this is simply by first order the entire list by list and priority, and then run the order by that goes through the rows one by one, like this:

SELECT TOP 1 * FROM (
    SELECT * FROM #Test ORDER BY list, priority
) ORDER BY (CASE WHEN list>@PreviousList THEN 1 ELSE 2 END)

But of course I cannot order by an already ordered subquery and get the error:

The ORDER BY clause is invalid in views, inline functions, derived tables,
subqueries, and common table expressions, unless TOP or FOR XML is also
specified.

Are there any ways and can get past this problem or get the query down to a single query and order by?

Upvotes: 1

Views: 79

Answers (4)

Brian Dishaw
Brian Dishaw

Reputation: 5825

Another possible solution is to use a subquery to select the min priority grouped by list and join it back to the table for the rest of the details

SELECT T2.*
FROM (SELECT MIN(priority) as priority, list
      FROM #Test 
      GROUP BY list) AS T1
INNER JOIN #Test T2 ON T1.list = T2.list AND T1.priority = T2.priority
ORDER BY T1.list, T1.priority

Upvotes: 1

Stan
Stan

Reputation: 983

Perhaps I am missing the requirement that makes this harder than I realize, but what about a nice simple join to select highest priority for the list. To scale, performance would require an index on list.

select t.* 
    , ttop.id as firstid
from #test t
  JOIN #test ttop on ttop.id = (SELECT TOP 1 ID 
                                FROM #TEST tbest 
                                 WHERE t.list = tbest.list order by priority)  
                                 and ttop.id = t.id -- this does the trick!

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

I want to step through rows one by one selecting the top one for each list ordered by priority, and start over when I get to the end.

You can use the built in ROW_NUMBER function that is designed for these scenarios with OVER(PARTITION BY name ORDER BY priority) to do this directly:

WITH CTE
AS
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY name ORDER BY priority) AS RN
  FROM #Test
)
SELECT * 
FROM CTE 
WHERE RN = 1;

The ranking number rn generated by ROW_NUMBER() OVER(PARTITION BY name ORDER BY priority) will rank each group of rows that has the same name ordered by priority then when you filtered by WHERE rn = 1 it will remove all the duplicate with the same name and left only the first priority.

Upvotes: 1

MS Kesavan
MS Kesavan

Reputation: 21

SELECT TOP 1 * FROM (
        SELECT * FROM #Test 
    ) ORDER BY (CASE WHEN list>@PreviousList THEN 1 ELSE 2 END)

Try this, because Order By is not allowed in CTE.

Upvotes: 0

Related Questions