CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Piyush
Piyush

Reputation: 419

SQL GROUPING on 2 fields to get the min value

I have wrote complex query and getting a data as shown below :

+----+---------+-------+------------+
| ID | TYPE_ID | VALUE | SORT_ORDER |
+----+---------+-------+------------+
|  1 |       1 | AAA   |          1 |
|  1 |       1 | BBB   |          2 |
|  2 |       1 | AA1   |          1 |
|  3 |       1 | BB2   |          1 |
|  3 |       1 | AA2   |          2 |
|  3 |       1 | BB3   |          3 |
|  4 |       2 | AAA1  |          1 |
|  4 |       1 | BB4   |          2 |
|  4 |       1 | AA4   |          3 |
|  5 |       2 | BB5   |          1 |
|  5 |       2 | AA5   |          2 |
+----+---------+-------+------------+

but I want to get the value for each ID with minimum sort_order and also consideing the Type_id 1 as higher priority. my group by is fetching above table for Min(sort_order) since I am including the Value in group by which is different for each row. Below is the expected result.

+----+---------+-------+------------+
| ID | TYPE_ID | VALUE | SORT_ORDER |
+----+---------+-------+------------+
|  1 |       1 | AAA   |          1 |
|  2 |       1 | AA1   |          1 |
|  3 |       1 | BB2   |          1 |
|  4 |       1 | BB4   |          2 |
|  5 |       2 | BB5   |          1 |
+----+---------+-------+------------+

Please give me your suggestions to achieve this.

Upvotes: 0

Views: 79

Answers (1)

Taryn
Taryn

Reputation: 247880

Since you are using SQL Server 2005+, the easiest way to get the result would be to use windowing functions. In this case you could use row_number() and partition the data by id but sort the data by type_id and sort_order:

;with cte as
(
  select id, type_id, value, sort_order,
    rn = row_number() over(partition by id order by type_id, sort_order)
  from yourtable
)
select id, type_id, value, sort_order
from cte
where rn = 1

See Demo. Once you've added the row_number() then you will only return the rows where the row number = 1.

Upvotes: 6

Related Questions