Query to display first record on duplicate records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query to display first record on duplicate records

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2015-02-06 : 22:02:33

Hi,

How do I query to display only the first records on any duplicate records?

StudentNum
1122
1122
2233
2233
4455
6565
6565
7777
7777

I want to just display
1122
2233
4455
6565
7777

Thank you.

SQLBoy

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-07 : 08:08:48

As a table is an unordered set, first is meaningless without a column(s) to order on.

With the data given, the result can be achived by:


SELECT DISTINCT StudentNum
FROM YourTable

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2015-02-07 : 12:22:07

Thanks Ifor, sorry I missed other columns information.

Its actually like this:
StudentNum StudentScore StudentAge
1122 75 18
1122 70 18
2233 80 20
2233 85 20
4455 90 19
6565 50 19
6565 75 19
7777 87 18
7777 92 18

So, I want to display all this records with the condition: the first StudentID only if there are more than StudentID value. So, it would be like this:

StudentNum StudentScore StudentAge
1122 75 18
2233 80 20
4455 90 19
6565 50 19
7777 87 18

Thanks

SQLBoy

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2015-02-08 : 00:51:07

I'd create a clustered index on the file then use that index for your order by, but in the example below I used a (select 1) to sort as that is a trick to take the order it evaluates in a ranking function (I'd prefer the CLUSTER ID though even though results should be identical)


declare @test table(id int PRIMARY KEY CLUSTERED (id) identity(1,1) ,studentnum int, studentscore int, studentage int)

insert into @test(studentnum,studentscore,studentage)
select 1122, 75, 18 union all
select 1122, 70, 18 union all
select 2233, 80, 20 union all
select 2233, 85, 20 union all
select 4455, 90, 19 union all
select 6565, 50, 19 union all
select 6565, 75, 19 union all
select 7777, 87, 18 union all
select 7777, 92, 18 

select *
from
(
select * ,ROW_NUMBER() over (partition by studentnum order by id) as rowid
from 
@test 
) aa
where aa.rowid =1 

select *
from
(
select * ,ROW_NUMBER() over (partition by studentnum order by (select 1)) as rowid
from 
@test 
) aa
where aa.rowid =1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources