CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Brad
Brad

Reputation: 1480

Get 10 highest ascending values of a column using Microsoft SQL Server

I am working with Microsoft SQL Server and found my self in a predicament. I'd like to basically do an order on an order. The current SQL query I have is:

SELECT top 10 labelid 
FROM mytable 
ORDER BY labelid DESC

This will return the top 10 values of the labelid column in descending order. I would like to however query the top 10 values, and have the results be in ascending order.

Is there an easy way of doing this without using procedures/plsql/etc?

Upvotes: 2

Views: 109

Answers (3)

Matt
Matt

Reputation: 14381

Lamak's solution is great and derived tables are lots of fun but one very helpful way of organizing your code when using Sql Server and other platforms that have them is Common Table Expressions [CTE] And while you are looking up/learning also check out Window Functions as they are helpful.

;WITH cte AS (
    SELECT
       labelid 
       ,ROW_NUMBER() OVER (ORDER BY labelid DESC) as RowNum
    FROM
       mytable 
)

SELECT *
FROM
    cte
WHERE
    RowNum <= 10
ORDER BY
    lableid

Upvotes: 1

Scott Dobbins
Scott Dobbins

Reputation: 294

 Select * from (
  SELECT top 10 labelid FROM mytable ORDER BY labelid DESC) order by labelid

Upvotes: 0

Lamak
Lamak

Reputation: 70678

You just need to use your query as a derived table:

SELECT *
FROM (SELECT TOP 10 labelid 
      FROM mytable 
      ORDER BY labelid DESC) A
ORDER BY labelid;

Upvotes: 3

Related Questions