Select latest 2 records - Select top?
-
I have a table that I want to get the latest 2 records for each item.
TABLE
NAME.....LAST_SEEN
joe.....1/1/2004
joe.....1/2/2004
joe.....1/3/2004
ken.....1/3/2004
ken.....1/4/2004
ken.....1/5/2004
bob.....1/3/2004
bob.....1/7/2004
bob.....1/9/2004
I want the results from my sql to be:
joe.....1/2/2004
joe.....1/3/2004
ken.....1/4/2004
ken.....1/5/2004
bob.....1/7/2004
bob.....1/9/2004
-
Think you can do it with a UNION query – something like:
select name, max(last_seen)
from test
group by name
union
select t1.name, max(t1.last_seen)
from test t1
inner join test t2
on t1.name = t2.name
where t1.last_seen < t2.last_seen
group by t1.name
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I would suggest UNION ALL instead of just UNION
Regards,
Sachin Dedhia -
You also could enumerate the groups within a group, and retrieve the ordinals from these enumerated groups that you arre interested in.
In this case You want the first two occurences within each group ordered by LAST_SEEN desc.
- Make sense eh!??
Well example follows:
select
NAME,
LAST_SEEN
from
( select
NAME,
LAST_SEEN,
cnt = ( select count(distinct tin.LAST_SEEN) from
tin
where tin.NAME = tout.NAME
and tin.LAST_SEEN >= tout.LAST_SEEN ) -- a)
from
tout
group by
NAME,
LAST_SEEN ) tcnt
where
cnt <= 2 -- b)
order by
NAME,
LAST_SEEN
a) this is where you specifiy the ordering - up or down. ( in this case down since we want the last two )
b) this is where you specify which ones you want.
if you were interested in the 2nd and 5th times LAST_SEEN you would write: ...where cnt in(2,5)...
/rockmoose
You must unlearn what You have learnt -
Makes a lot of sense
Regards,
Sachin Dedhia -
just a simple group by name,date will produce the result, why union and all and ofcourse top 2 will , and order by date desc
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Sukhoi, select top2 ..... group by name, date order by date desc
will not work.
To get the result for a specific name you could do it like you suggest, but not for all the names in one resultset. eg:
select top2 ..... group by name, date
where name = 'joe'
order by name, date desc
just for 'joe'
/rockmoose
You must unlearn what You have learnt -
yep you are right my technique doesn't work later i tried it out. u r does
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Thank you all so much for your help. I have it working now due to your efforts!
-
Here's the most elegant way to do this, but it requires a identity column:
Select *
from #Table t
where ID in
(Select top 2 ID
From #Table t1
Where t.Name = t1.Name
order by Last_Seen Desc)
Use a correlated subquery in your where clause that selects the top 2 records
if object_ID('tempdb..#Table') is not null drop table #Table
create table #TABLE (ID int identity Not null, NAME varchar(25), LAST_SEEN datetime)
insert #Table Values ('joe', '1/1/2004')
insert #Table Values ('joe', '1/2/2004')
insert #Table Values ('joe', '1/3/2004')
insert #Table Values ('ken', '1/3/2004')
insert #Table Values ('ken', '1/4/2004')
insert #Table Values ('ken', '1/5/2004')
insert #Table Values ('bob', '1/3/2004')
insert #Table Values ('bob', '1/7/2004')
insert #Table Values ('bob', '1/9/2004')
Signature is NULL
-
Yes,
And actually the correlated subquery technique only requires the table to have a Primary Key ( Or Unique Constriant ) defined on 1 column.
The requirement is that the Uniqueness is defined on only one column ( IN clause ).
/rockmoose
You must unlearn what You have learnt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply