CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Buyukcaglar
Buyukcaglar

Reputation: 5

Select certain rows with respect to their min max values

In T-SQL (MSSql 2008R2) I Would like to select certain rows from a table or set of results;

StoreId StoreName BrochureId PageId Rank Distance
43561   X         1627       11608  73   598.10
43561   X         1627       11591  68   598.10
43561   X         1627       11615  41   598.10
43827   Y         1727       21708  75   1414.69
43827   Y         1727       21591  62   1414.69
43827   Y         1727       21615  44   1414.69
43919   Z         1827       31809  77   2487.35
43919   Z         1827       31591  60   2487.35
43919   Z         1827       31615  39   2487.35

Would like to select only rows with lowest distance and with the highest rank, as such;

StoreId StoreName BrochureId PageId Rank Distance
43561   X         1627       11608  73   598.10
43827   Y         1727       21708  75   1414.69
43919   Z         1827       31809  77   2487.35

Thank you for your help.

Upvotes: 0

Views: 38

Answers (2)

JamieD77
JamieD77

Reputation: 13949

You can use ROW_NUMBER for this.

SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY StoreName ORDER BY Distance, [Rank] DESC) Rn  
    FROM 
        Table1
) t
WHERE Rn = 1

there are other ranking functions you can use as well.. for example, if you use RANK instead of ROW_NUMBER here, you can include ties in your result as well.

SQL Ranking Functions

Upvotes: 1

pmbAustin
pmbAustin

Reputation: 3970

Use a correlated subquery in your where clause... assuming your table name is MyTable, something like this should get what you want:

SELECT [StoreId], [StoreName], [BrochureId], [PageId], [Rank], [Distance]
  FROM MyTable m
 WHERE [Rank] = (SELECT MAX([Rank]) FROM MyTable x WHERE x.StoreId = m.StoreId)
    OR [Distance] = (SELECT MIN([Distance]) FROM MyTable y WHERE y.StoreId = m.StoreId)

(note, I enclosed the column names in square brackets because "Rank" is a reserved SQL Keyword)

Upvotes: 0

Related Questions