MAX minus 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

SQL Server Development (2000)

MAX minus one

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-03-16 : 08:34:32

Hello all.

This is my second posting here. The replies to my last post were extremely helpful....thanks!!!

Anyway

I'm trying to select the 2nd most recent entry by date. I would like to select the MAX minus 1 to eventually put in #table and perform a DATEDIFF on. Any help would be greatly appreciated.

Thanks
Callaway

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 08:38:39

Or

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Table1 ORDER BY EntryDate DESC) AS x ORDER BY EntryDate

Peter Larsson
Helsingborg, Sweden

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-03-16 : 08:45:35

Thanks PESO...That works..
I should have mentioned this in the first place.....after slecting the last 2 most recent entries, is there a way to a datediff on them w/o having to use a #table? That's what I'm ultimatley tryting to accomplish.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 08:49:29

Do you need all columns from the table?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 08:54:57

[code]-- Prepare sample data
DECLARE @Sample TABLE (Col1 INT, Col2 INT, EntryDate DATETIME)

INSERT @Sample
SELECT 1, 2, '20070301' UNION ALL
SELECT 2, 7, '20070310' UNION ALL
SELECT 7, 3, '20070315'

-- Show the expected output
SELECT TOP 1 Col1,
Col2,
EntryDate,
DATEDIFF(DAY, EntryDate, (SELECT MAX(EntryDate) FROM @Sample)) AS Days
FROM (
SELECT TOP 2 Col1,
Col2,
EntryDate
FROM @Sample
ORDER BY EntryDate DESC
) AS x
ORDER BY EntryDate[/code]
Peter Larsson
Helsingborg, Sweden

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-03-16 : 08:59:51

Wow..Thanks.

I'll give it a go

Brendan
New Jersey, USA

Kristen
Test

22859 Posts

Posted - 2007-03-16 : 10:11:49

If no other columns needed:


SELECT	[First] = MIN(EntryDate),
	[Last] = MAX(EntryDate),
	[Diff] = DATEDIFF(DAY, MIN(EntryDate), MAX(EntryDate))
FROM	(
		SELECT TOP 2	EntryDate
		FROM		@Sample
		ORDER BY	EntryDate DESC
	) AS x

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-16 : 11:51:54

New Jersey!

What exit?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-03-16 : 12:36:41

My new Jersey has exits for head, each arm and my torso ...

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-16 : 13:13:35

quote:
Originally posted by Kristen

exits for head

That has got to be a very stange jersey

test

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-03-16 : 14:08:30

Exit 98
haaha

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-16 : 14:48:54

145

http://en.wikipedia.org/wiki/Garden_State_Parkway

ahhh, those summer days in the before life

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources