CODEWITHSUNDEEP
sqlsql-server
TheMartianGuy
TheMartianGuy

Reputation: 87

Select only one row with same key value from a table

I have this table:

reg_no    |    cname    |  no
1         |    X        |  1
1         |    Y        |  2
2         |    X        |  1
2         |    Y        |  2

What I want to do is to select all that rows but I only want one row for each reg_no when I arrange it in desc (it should only get the row with highest no for each reg_no).

The output should be:

1 Y 2
2 Y 2

Upvotes: 0

Views: 1119

Answers (2)

Kirill Slatin
Kirill Slatin

Reputation: 6173

In MSSQL using CROSS APPLY this would be

SELECT DISTINCT 
    r1.reg_no, r2.cname, r2.no
FROM
    table_name r1 
CROSS APPLY 
(SELECT TOP 1
    r.cname, r.no
FROM 
    table_name r
WHERE r1.reg_no = r.reg_no 
ORDER BY r.no DESC) r2

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Use Row_Number() window function

select Reg_no,C_name,no from
(
select row_number() over(partition by reg_no order by no desc) Rn,*
from yourtable
) A
where rn=1

or ANSI SQL standard will work in sql server 2000. Find the max no in each reg_no then join the result back to the main table.

select A.Reg_no,A.C_name,A.no 
from yourtable As A
Inner Join
(
select max(no) As no,Reg_no
from yourtable
group by Reg_No
) As B
on A.No=B.No and A.Reg_No=B.Reg_no

Upvotes: 2

Related Questions