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
 Old Forums
 CLOSED - General SQL Server
 SELECTING LAST 2 ROWS OF A SUBSET

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-25 : 09:41:53
SHERYL writes "I WOULD LIKE TO SELECT THE MAX 2 DATES IN A GROUP

MY TABLE LOOKS LIKE THIS

PENDING_ID
PENDING_DATE
PENDING_TXT

I WOULD LIKE THE LAST/MAX 2 DATES GROUPED BY PENDING_ID

THIS WOULD GIVE ME 2 ROWS FOR EACH PENDING_ID (IF THE PENDING_ID HAS MORE THAN ONE DATE OFCOURSE)"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-10-25 : 09:50:18
WHY ARE WE SHOUTING?

No need for that . Post the DDL for the table and give a few sample rows from your table and we'll help you out.

Jonathan
{0}
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-25 : 10:40:04

create table #temp (
someid int,
somevc varchar(10) )

insert into #temp
select 1,'a'
union select 1,'b'
union select 1,'c'
union select 1,'d'
union select 1,'f'
union select 1,'g'
union select 1,'h'
union select 1,'i'
union select 2,'a'
union select 2,'b'
union select 2,'c'
union select 2,'d'
union select 2,'e'
union select 2,'f'
union select 2,'g'
union select 2,'h'
union select 2,'i'
union select 2,'j'

select
someid,
somevc
from
#temp t
where
exists (
select 1
from
#temp
where
someid = t.someid and
somevc = t.somevc
group by
someid
having (
select
count(*)
from
#temp
where
someid = t.someid and
somevc > t.somevc ) < 2 )

 


Jay White
{0}
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-10-25 : 10:44:17
You see that? Hungry posters will even answer other's questions for you!

Jonathan
{0}
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-25 : 17:29:38
I quite like this:


SELECT someid, somevc
FROM (
SELECT someid, CASE WHEN col = 0 THEN somevc0 ELSE somevc1 END somevc
FROM (
SELECT T0.someid, MAX(T0.somevc) somevc0, MAX(T1.somevc) somevc1
FROM (
SELECT someid, MAX(somevc) somevc
FROM #temp
GROUP BY someid) T0
LEFT JOIN #temp T1 ON T0.someid = T1.someid AND T0.somevc > T1.somevc
GROUP BY T0.someid
) a
CROSS JOIN (SELECT 0 AS col UNION ALL SELECT 1) n
) a
WHERE somevc IS NOT NULL



Go to Top of Page
   

- Advertisement -