CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jinlong
jinlong

Reputation: 839

Conditional Group By in SQL

I have the following table that I want to group by type. When there are multiple rows with the same type (e.g., A & B type), I want to preserve the 'value' from the row with the highest rank (i.e., primary > secondary > tertiary..)

rowid | type  | rank       | value
    1 | A     | primary    |  1
    2 | A     | secondary  |  2

    3 | B     | secondary  |  3
    4 | B     | tertiary   |  4

    5 | C     | primary    |  5

So the resulting table should look like

rowid | type | rank      | value
    1 | A    | primary   | 1
    3 | B    | secondary | 3
    5 | C    | primary   | 5

Any suggestions will be highly appreciated!

p.s., I'm working in MS SQL Server.

Upvotes: 1

Views: 938

Answers (3)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Another way of doing is with Row_Number and an Order By specifying your rule with CASE.

Schema:

CREATE TABLE #TAB(rowid  INT, [type] VARCHAR(1), rankS VARCHAR(50)   ,      value INT)
INSERT INTO #TAB
SELECT 1 , 'A' , 'primary' , 1
UNION ALL
SELECT 2 , 'A' , 'secondary', 2
UNION ALL
SELECT 3 , 'B' , 'secondary' , 3
UNION ALL
SELECT 4 , 'B' , 'tertiary' , 4
UNION ALL
SELECT 5 , 'C' , 'primary' , 5

Now apply rank rule with Row_Number

SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY [type] ORDER BY (CASE rankS
WHEN 'primary' THEN 1
WHEN 'secondary' THEN 2
WHEN 'tertiary' THEN 3 END )) AS SNO, * FROM #TAB
)A
WHERE SNO =1

Result:

+-----+-------+------+-----------+-------+
| SNO | rowid | type |   rankS   | value |
+-----+-------+------+-----------+-------+
|   1 |     1 | A    | primary   |     1 |
|   1 |     3 | B    | secondary |     3 |
|   1 |     5 | C    | primary   |     5 |
+-----+-------+------+-----------+-------+

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

try this,

;WITH CTE
AS (
    SELECT *
        ,row_number() OVER (
            PARTITION BY [type] ORDER BY value
            ) rn
    FROM @t
    )
SELECT *
FROM cte
WHERE rn = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can use row_number(). Here is a simple'ish method:

select t.*
from (select t.*,
             row_number() over (partition by type
                                order by charindex(rank, 'primary,secondary,tertiary')
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

This uses charindex() as a simple method of ordering the ranks.

Upvotes: 4

Related Questions