1

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
0
1

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

2
  • So I'll have to create a temp table with my strings and rankings in them? – John Ligda Nov 14 '14 at 20:18
  • 'dog' - 1,'cat' - 2,'bird' - 3 etc? – John Ligda Nov 14 '14 at 20:19
1

Does this do what you're after?

SELECT TOP (1) WITH TIES
    TX.COL1,
    TX.COL2,
    TX.COL3,
    TX.COL4
FROM dbo.TESTX AS TX
ORDER BY
    ROW_NUMBER() OVER (
        PARTITION BY TX.COL1
        ORDER BY
            -- Priorities
            CASE COL2
                WHEN 'dog' THEN 1
                WHEN 'cat' THEN 2
                WHEN 'bird' THEN 3
                ELSE 999
            END);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.