开发者

How to get one unique record from the same list of records from table? No Unique constraint in the table

开发者 https://www.devze.com 2023-03-19 02:04 出处:网络
I have one query in SQL Server output, Suppose i have one table (Ex.StudentMaster) having some fields-No unique constraints.

I have one query in SQL Server output,

Suppose i have one table (Ex.StudentMaster) having some fields-No unique constraints. For Ex. RollNumber and Name The table has same same data. For ex:

RollNo      Name
    1       Yoko
    1       Yoko
    1       Yoko

I want to get only third record. How can 开发者_JS百科i identify this unique record?


Any row is a third row :-)

create table test
(
n int,
name varchar(30)
);

insert into test values(1,'yoko'),(1,'yoko'),(1,'yoko');

select ROW_NUMBER() over(order by name) as ordinal, * from test;

Deleting the "third" row :-)

with a as
(
select ROW_NUMBER() over(order by name) as ordinal, * from test
)
delete from a where a.ordinal = 3

Deleting the last row:

with a as
(
select ROW_NUMBER() over(order by name) as ordinal, * from test
)
delete from a where a.ordinal = (select MAX(ordinal) from a)


You can use DISTINCT which return's distinct combination's of columns.

SELECT DISTINCT RollNo, Name
FROM mytable
0

精彩评论

暂无评论...
验证码 换一张
取 消