Help to write query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help to write query

Author

Topic

capellone
Starting Member

3 Posts

Posted - 2010-03-18 : 08:23:59

Hello,
I have a table Players with several columns: ID, DateAdded, PlayerID, PlayerName, PlayerSalary. The table has several records for the same player (same values in the last 3 columns, different in the first 2). I want to write a query to get back only 1 record for each player (for each PlayerID value) and i want that record to be the last one added (the one with the greatest value in column DateAdded). Could you help, please? I know that this is not the best design for the table, but for the moment i have to work with it as is.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 08:28:02

select ID, DateAdded, PlayerID, PlayerName, PlayerSalary
from
(
select
row_number() over (partition by PlayerID order by DateAdded desc) as rownum,
*
from Players
)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

capellone
Starting Member

3 Posts

Posted - 2010-03-18 : 08:35:33

quote:
Originally posted by webfred

select ID, DateAdded, PlayerID, PlayerName, PlayerSalary
from
(
select
row_number() over (partition by PlayerID order by DateAdded desc) as rownum,
*
from Players
)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you for your quick response; however, i am new to SQL programming and the code you posted makes little sense to me. Could you post some standard SQL code which does the same thing? Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 09:12:50

[code]declare @Players table (ID int, DateAdded datetime, PlayerID int, PlayerName varchar(255), PlayerSalary money)
insert @Players
select 1,'20100101',1,'Fred',null union all
select 2,'20100102',1,'Fred',null union all
select 3,'20100101',2,'Web',null union all
select 4,'20100105',2,'Web',null union all
select 5,'20100301',3,'Yak',null union all
select 6,'20100102',3,'Yak',null

select ID, DateAdded, PlayerID, PlayerName, PlayerSalary
from @Players p1
where DateAdded = (select max(DateAdded) from @Players p2 where p2.PlayerID = p1.PlayerID)[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

capellone
Starting Member

3 Posts

Posted - 2010-03-18 : 09:15:44

quote:

Originally posted by webfred

declare @Players table (ID int, DateAdded datetime, PlayerID int, PlayerName varchar(255), PlayerSalary money)
insert @Players
select 1,'20100101',1,'Fred',null union all
select 2,'20100102',1,'Fred',null union all
select 3,'20100101',2,'Web',null union all
select 4,'20100105',2,'Web',null union all
select 5,'20100301',3,'Yak',null union all
select 6,'20100102',3,'Yak',null

select ID, DateAdded, PlayerID, PlayerName, PlayerSalary
from @Players p1
where DateAdded = (select max(DateAdded) from @Players p2 where p2.PlayerID = p1.PlayerID)

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 09:17:09

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources