Second Latest date (Max -1) - 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)

Second Latest date (Max -1)

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-01 : 07:57:56

Hi Fellow Code Warriors

I was wondering how one could do the following:

I have a table that has the following data:

Ent_Nbr Date
En1 23/05/2004
En1 25/07/2004
En1 05/08/2004
En1 03/12/2004
EN2 03/01/2004
EN2 18/09/2004
EN2 23/05/2004
EN4 29/04/2004
EN4 03/12/2004
EN4 21/04/2004
EN4 05/08/2004
EN4 23/05/2004
EN4 26/09/2004

What method can I use to select the latest and the the second latest date?

Ent_Nbr Latest Date
Ent_Nbr Second Latest Date

For each ent_nbr

You can't teach an old mouse new clicks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-01 : 07:59:35

Did the submit form give you trouble:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46521
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46522

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-01 : 08:26:38

Machine froze during submit process

You can't teach an old mouse new clicks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-01 : 09:14:28

Try this

Select Top 2 * from Table group by Ent_Nbr, Date order by Date Desc

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 13:24:33

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Ent_Nbr char(3), [Date] datetime)
GO

SET DATEFORMAT dmy
GO

INSERT INTO myTable99(Ent_Nbr, [Date])
SELECT 'En1', '23/05/2004' UNION ALL
SELECT 'En1', '25/07/2004' UNION ALL
SELECT 'En1', '05/08/2004' UNION ALL
SELECT 'En1', '03/12/2004' UNION ALL
SELECT 'EN2', '03/01/2004' UNION ALL
SELECT 'EN2', '18/09/2004' UNION ALL
SELECT 'EN2', '23/05/2004' UNION ALL
SELECT 'EN4', '29/04/2004' UNION ALL
SELECT 'EN4', '03/12/2004' UNION ALL
SELECT 'EN4', '21/04/2004' UNION ALL
SELECT 'EN4', '05/08/2004' UNION ALL
SELECT 'EN4', '23/05/2004' UNION ALL
SELECT 'EN4', '26/09/2004'
GO

SELECT Ent_Nbr, 'MAX_Date-0' AS DateType, MAX([Date]) AS MAX_Date
FROM myTable99
GROUP BY Ent_Nbr
UNION ALL
SELECT Ent_Nbr, 'MAX_Date-1' AS DateType, MAX([Date]) AS MAX_Date
FROM myTable99 o
WHERE [Date] < (SELECT MAX([Date]) FROM myTable99 i WHERE o.Ent_Nbr = i.Ent_Nbr)
GROUP BY Ent_Nbr
ORDER BY Ent_Nbr, 3 DESC
GO

SET DATEFORMAT mdy
SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-02 : 01:22:02

Thanks a million Brett - the solution is perfect and pretty fast

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources