Sql-server – TSQL substitute for MAX() string ranking

sql servert-sql

I have a table with repeating values (col1) and I need to return col2 based on a ranking system. In my example the ranking system is handled by MAX() giving me the highest value. I then return the full row where col2 is of the highest value. Simple. But what if instead of numbers in col2 I have a set of strings, 'dog','cat','bird'. I want only the dog row for distinct values in col1. If there is no 'dog' row I'll take the 'cat' row. And finally if no cat I take the 'bird' row.

I'm looking for the string equivalent of MAX where I can rank the strings. Do I have to write a UDF? Can I do it with set based operation instead? 

CREATE TABLE TESTX (
    COL1 VARCHAR(56),
    COL2 VARCHAR(56),
    COL3 VARCHAR(56),
    COL4 VARCHAR(56)
)

INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (1,1,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (1,3,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (2,2,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (2,2,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,1,1,1)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,2,2,2)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,3,3,3)

SELECT * FROM TESTX

WITH CTE_X AS (
   SELECT 
      COL1, MAX(COL2) AS COL2
   FROM TESTX
   GROUP BY COL1)
SELECT DISTINCT 
    A.COL1, A.COL2, A.COL3, A.COL4
FROM 
    TESTX A
JOIN 
    CTE_X B ON A.COL1 = B.COL1 AND A.COL2 = B.COL2

Best Answer

Use RANK() function

with CTE_X as 
(
  select *, 
  RANK() over (partition by Col1
             order by Col2 DESC) rnk
  from t

) select * from CTE_X  where rnk = 1 ;

For reference (BOL) http://msdn.microsoft.com/en-us/library/ms176102.aspx

Related Question