Next Maximum Column - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Next Maximum Column

Author

Topic

bluemetal
Starting Member

26 Posts

Posted - 2005-08-17 : 01:37:27

Hi Guys:

Consider the following Sample Table:

POL ID DATE
001 1 21/05/2005
001 3 25/05/2005
002 4 30/05/2005
002 5 02/06/2005
002 8 10/06/2005
003 9 12/06/2005

Can i do a Query that returns me a result with each of these rows and the row with the next maximum date? (Grouped by POL) ... Something like this:

POL ID DATE ID DATE
001 1 21/05/2005 3 25/05/2005
001 3 25/05/2005 3
002 4 30/05/2005 5 02/06/2005
002 5 02/06/2005 8 10/06/2005
002 8 10/06/2005

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-17 : 02:10:19

[code]select a.pol, max(a.date)
from sampleTable a
join (Select b.pol, max(b.date) as maxDate
from SampleTable b
group by b.pol
) as gb on a.pol = gb.pol
where a.date < gb.Date[/code]This query needs work for pols with just one date, but I have to go now. If no one fix it until I come back, I'll do it later.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 02:21:07

Try this also


Select *, (select top 1 id from yourtable where pol=T.pol and date>T.date) as MaxId, 
(select top 1 date from yourtable where pol=T.pol and date>T.date) as MaxDate from yourtable T

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-18 : 06:11:19

Ok, my query can be improved using outer join, but the question is how do you want to handle duplicates, I mean when more then one row for the same pol reaches the same max date value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources