0

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 i identify this unique record?

Yoko Zunna
  • 1,804
  • 14
  • 21
  • What version of SQL Server? It matters if 2005+, or not. And how you determine what is the latest row when there's nothing available to distinguish between duplicates. – OMG Ponies Jul 11 '11 at 04:33
  • Does it matter's if it is first, second or third row? – niktrs Jul 11 '11 at 04:35

2 Answers2

3

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)
Michael Buen
  • 38,643
  • 9
  • 94
  • 118
  • 1
    That's how I'd do it, but it is SQL Server 2005+ functionality. – OMG Ponies Jul 11 '11 at 04:35
  • I put the answer anyway, better be FGITW than wait eon for his answer what version he is using, when for all we know, he might be using the latest version ;-) – Michael Buen Jul 11 '11 at 04:39
  • In case of SQL server 2000(!) http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm – niktrs Jul 11 '11 at 04:45
  • Thanks, i got the Solution with "select ROW_NUMBER() over(order by name) as ordinal, * from test" – Yoko Zunna Jul 11 '11 at 09:05
1

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

SELECT DISTINCT RollNo, Name
FROM mytable
niktrs
  • 9,858
  • 1
  • 30
  • 30
  • Yes,this is true. But the thing is i want a particular record only. Using DISTINCT will give me single record but i will not be knowing which record it is, First,second or third. – Yoko Zunna Jul 11 '11 at 08:46