Reputation: 419
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
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