MAX less one - 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)

MAX less one

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2008-11-27 : 06:20:57

I am trying to get a set of records with values of MAX but one. For example, for record 1 I want the ModDate value before the most recent (which I can get with MAX(ModDate) AS MostRecentModDate).

I have tried the following but this takes an extremely long time to execute:

SELECT m1.ID, m1.MAX(ModDate)
FROM ModTab m1 INNER JOIN
(SELECT ID,MAX(ModDate) AS ModDate
FROM ModTab) MostRecentMod ON m1.ModDate <> MostRecentMod.ModDate

Is there anything else I have not tried?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-27 : 06:38:25

[code][code]SELECT
A.[ID],B.MAXDATE2
FROM
(SELECT
[ID]=M1.[ID],
MAX(M1.MODDATE) AS MAXDATE

FROM
MODTAB M1 )A

JOIN

(SELECT
[ID]=M1.ID,
MAX(M1.MODDATE) AS MAXDATE2
FROM
MODTAB M1
WHERE
MODDATE<A.MAXDATE)B

ON A.[ID]=B.[ID][/code][/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 12:30:34

[code]SELECT ID,ModDate
FROM
(
SELECT t.ID,
t.ModDate,
(SELECT COUNT(*)
FROM Table
WHERE ID=t.ID
AND ModDate>t.ModDate)+1 AS Seq
FROM Table t
)r
WHERE Seq=2
[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-28 : 06:31:30

Select ID,Min(ModDate) from
(
SELECT top 2 id,ModDate
FROM Table
order by ModDate desc
)a

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-28 : 06:35:25

quote:
Originally posted by darkdusky

Select ID,Min(ModDate) from
(
SELECT top 2 id,ModDate
FROM Table
order by ModDate desc
)a

This wouldn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 06:36:45

quote:
Originally posted by sakets_2000

quote:
Originally posted by darkdusky

Select ID,Min(ModDate) from
(
SELECT top 2 id,ModDate
FROM Table
order by ModDate desc
)a

This wouldn't work.

thats true. the inner query will return top 2 records alone based on moddate but not for each id.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-28 : 07:05:37

Whoops I didn't realize he wanted Max-1 for each ID !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources