How to select record in priority Order if there are multiple record in table

Please help me with a solution,

i have a table where it has a value like
column1 columnn2
1 a
1 b
1 c
2 c
2 b

then i should get
1 a
2 b

where if there multiple record found then priority Order is a,b,c

Not sure if it is what you desired, but try it

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;

output:

column1 column2
1 a
2 b

dbfiddle here

order by column1,column2

or TOP 1

please explain how you want to get
1 a
2 b

hi friend,

Good Morning!!

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

the o/p should be

1 promise
2 memo

this is my scenario Harishgg

in case if the priority order is
Promise, Memo , Customer.
i.e.
1 Customer
1 Memo
1 Promise
2 Customer
2 Memo

the o/p should be

1 promise
2 memo

i want to rank in the mentioned priority order

hi

Here is the solution
Please check

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
Result

image

Thank you Harishgg it works :slight_smile: