Get Newest Row using secondary column for unique

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

Get Newest Row using secondary column for unique

Author

Topic

rickincanada
Starting Member

18 Posts

Posted - 2011-03-28 : 00:31:05

Hi there,

I am fairly new to SQL and am finding myself a little stuck on the following query. My table looks something like this:

ID Name Sub_ID Date
124 Jones 555 3/27/2011
125 Jones 555 3/28/2011
126 Jones 555 3/29/2011
127 Smith 685 3/29/2011
128 Johnson 959 3/29/2011
129 Johnson 959 3/30/2011

What I'm trying to do is get only the rows back which the most recent date based on the 'Sub_ID' AND the 'Name' being the same. Something like this:

ID Name Sub_ID Date
126 Jones 555 3/29/2011
127 Smith 685 3/29/2011
129 Johnson 959 3/30/2011

Is this possible and if so how? Let's assume the table name is 'transactions'. Thanks for any help you can provide!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-28 : 01:54:54

--Try this
create table #transactions(id int, name nvarchar(25), sub_id int,dates datetime)
insert into #transactions values ('124','Jones', '555', '3/27/2011')
insert into #transactions values ('125' ,'Jones', '555' ,'3/28/2011')
insert into #transactions values ('126', 'Jones', '555' ,'3/29/2011')
insert into #transactions values ('127', 'Smith', '685', '3/29/2011')
insert into #transactions values ('128', 'Johnson', '959', '3/29/2011')
insert into #transactions values ('129', 'Johnson', '959', '3/30/2011')

-- Is this you are looking for
select * from #transactions where dates>convert(varchar,getdate()-1,100)
select * from #transactions where dates>convert(varchar,getdate()+1,100)
select Id,name,sub_id,convert(varchar,dates,103) from #transactions where dates>convert(varchar,getdate()+1,100)
--------------------Finally This works posted by -----------------
select *
from (
select *, row_no = row_number() over (partition by Sub_ID order by Dates desc)
from #transactions
) t
where t.row_no = 1

Raghu' S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-28 : 02:35:15

[code]
select *
from (
select *, row_no = row_number() over (partition by Sub_ID order by Date desc)
from transactions
) t
where t.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources