CREATE TABLE Table1
([column1] int, [column2] varchar(1))
;
INSERT INTO Table1
([column1], [column2])
VALUES
(1, 'a'),
(1, 'b'),
(1, 'c'),
(2, 'c'),
(2, 'b')
;
SELECT
column1
,column2
FROM
(
SELECT column1, column2
,ROW_NUMBER()OVER(PARTITION BY column1 ORDER BY column2 ASC) AS rn
FROM Table1 AS T
) as S
WHERE S.rn = 1;
in this case if it is a,b then it can sort in case if the priority order is
Promise, Memo , Customer.
i.e.
1 Customer
1 Memo
1 Promise
2 Customer
2 Memo
Looks like my solution is same as Stepson .. slightly different
Create Data Script
USE tempdb
go
drop table #data
go
create table #data
(
id int,
status varchar(100)
)
go
insert into #data select 1,'Customer'
insert into #data select 1,'Memo'
insert into #data select 1,'Promise'
insert into #data select 2,'Customer'
insert into #data select 2,'Memo'
go
SQL
SELECT *
FROM (SELECT Row_number()
OVER(
partition BY id
ORDER BY CASE b.status WHEN 'Promise' THEN 1 WHEN 'Memo' THEN 2 WHEN 'Customer' THEN 3 END ) AS rn,
b.*
FROM #data b) a
WHERE a.rn = 1