CODEWITHSUNDEEP
sqlsql-server-2008
Andrew Walters
Andrew Walters

Reputation: 4803

Sql Distinct Top

I have a query which is currently structured like this:

Select top 10 ID, CalculatedColumn
From Table

The calculated column is build by adding together some fields in tables we join to.

The data returned would look like this: 

 +----------------------------------------------+
 | Id           Calculated Column               |
 |----------------------------------------------|
 | 1            10                              |
 | 2            11                              |
 | 3            12                              |
 | 4            13                              |
 | 5            14                              |
 | 6            14                              |
 | 7            14                              |
 | 8            14                              |
 | 9            14                              |
 | 10           14                              |
 +----------------------------------------------+

I want to change the query, so that selecting the top 5 rows yields the current result. That is, I want to select the top 5 distinct calculated values.

All the samples I've run across deal with selecting distinct ID's that you can turn around and join against vs. values.

Is there a way you can do this in SQL?

Upvotes: 0

Views: 72

Answers (2)

Michael Petito
Michael Petito

Reputation: 13171

You could use the RANK function over your computed column. Computed values which tie will have the same rank.

For example:

SELECT Id, ComputedColumn
FROM
(
    SELECT Id, ComputedColumn, RANK() OVER (ORDER BY ComputedColumn) Rnk
    FROM TableName
) x
WHERE Rnk <= 5

Upvotes: 2

wvdz
wvdz

Reputation: 16651

A very straightforward possibility is like this:

WITH cte AS
(
  SELECT TOP 5 CalculatedColumn
  FROM Table1
  GROUP BY CalculatedColumn
  ORDER BY CalculatedColumn
)
SELECT * FROM Table1 t
WHERE EXISTS
  (SELECT 1 FROM cte WHERE CalculatedValue = t.CalculatedValue)
ORDER BY CalculatedValue, ID

Upvotes: 1

Related Questions