Bringing back 2nd Highest Id in a group by clause.

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

Bringing back 2nd Highest Id in a group by clause.

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-01 : 06:49:57

Hi All,

can anyone tell me a nice efficient way to do the following..

I have 2 tables ([tableA] and [tableB]), tableB has a foreignKey to A and has a running identity column. This means it's easy to identity the most recent piece of data put into it for each instance of A.

The following code does this...


DECLARE @tableA TABLE (
	[Id] INT
	, [data] VARCHAR(50)
	)

INSERT INTO @tableA
      SELECT 1,'someDataA'
UNION SELECT 2,'someDataB'
UNION SELECT 3,'someDataC'

DECLARE @tableB TABLE (
	[Id] INT IDENTITY(1,1)
	, [tableAId] INT
	, [moreData] VARCHAR(50)
	)

-- Data Inserts for A
INSERT INTO @tableB SELECT 1, 'firstData A'
INSERT INTO @tableB SELECT 1, 'moreData A'
INSERT INTO @tableB SELECT 1, 'evenMoredata A'
INSERT INTO @tableB SELECT 1, 'lastData A'

-- Data Inserts for B
INSERT INTO @tableB SELECT 2, 'firstData B'
INSERT INTO @tableB SELECT 2, 'lastData B'

-- Data Inserts for C
INSERT INTO @tableB SELECT 3, 'firstData C'


SELECT
	a.[ID]
	, a.[data]
	, b.[moreData]
FROM
	@tablea a

	JOIN (
		SELECT
			[tableAId] AS tableAId
			, MAX([Id]) AS tableBId
		FROM
			@tableb
		GROUP BY
			[tableAId]
		)
		hTableB ON hTableB.[tableAId] = a.[Id]

	JOIN @tableb b ON b.[Id] = hTableB.[tableBId]

This produces the following results....


ID	data	moreData
1	someDataA	lastData A
2	someDataB	lastData B
3	someDataC	firstData C

What I want is to return the 2nd most recent [Id] for each entry in A.

The results I need are these


ID	data	moreData
1	someDataA	evenMoredata A
2	someDataB	firstData B
3	someDataC	(NULL)

Thanks in advance,

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-01 : 07:02:13

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

pootle_flump

1064 Posts

Posted - 2008-08-01 : 07:05:49

quote:
Originally posted by madhivanan

Which version of SQL Server are you using?

Hmm - good point - forgot about OVER(). Anyhoo - version agnostic:

SELECT
	a.[ID]
	, a.[data]
	, b.[moreData]
FROM
	@tablea a

	LEFT OUTER JOIN (
					SELECT
						[tableAId] AS tableAId
						, MAX([Id]) AS tableBId
					FROM
						@tableb AS a
					WHERE		id <> (	SELECT	MAX([Id])
										FROM
											@tableb AS b
										WHERE	b.[tableAId] = a.[tableAId])
					GROUP BY
						[tableAId]
					)
		hTableB ON hTableB.[tableAId] = a.[Id]

	LEFT OUTER JOIN @tableb b ON b.[Id] = hTableB.[tableBId]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-01 : 07:18:40

madhivanan : A mix. some clients on sqlserver 2000, some on 2005.

pootle_flump : Thanks, works fine.

Is there any nice way to get, say the 4th, 5th etc records rather than 2nd? ( can you do recursive subqueries in a situation like this? and if so would it be advisable?)

-------------
Charlie

pootle_flump

1064 Posts

Posted - 2008-08-01 : 07:38:55

Not really - not in 2k - certainly not without dynamic SQL. There are ways of course - just not what I would describe as "nice". There are several elegant methods in SQL 2005, especially using ROW_NUMBER() which is just perfect for the job.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-01 : 07:56:05

You can modify this with your requirement

declare @result table(sno int identity(1,1), id int, moredata varchar(100))

insert into @result(id, moredata)
select tableAid,moredata from
(
select * from @tableB t where moredata in
(select top 2 moredata from @tableB where tableAid=t.tableAid order by id desc)
) as t2
where tableAid in (select tableAid from @tableB group by tableAid having count(*)>=2)

select t1.id, t1.data, t2.moredata from @tableA as t1
left outer join @result as t2 on t1.id=t2.id and sno%2=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources