Maximum date in a group of records

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)

Maximum date in a group of records

Author

Topic

mohdrashid
Starting Member

23 Posts

Posted - 2004-05-06 : 23:57:17

hi all,
i got is table:
Id StartDate EndDate
a 19/03/2001 18/03/2002
a 19/03/2002 18/04/2002
b 13/08/2000 12/08/2001
b 13/08/2001 12/08/2002
b 13/08/2002 10/07/2002

Sort command and groupins i am ok but i need to select only the records that has the latest enddate. (See *)
any ideas? thanks in advance
rashid

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-05-07 : 00:11:34

Does this query work for you?

SELECT someTable.ID, someTable.StartDate, someDate.EndDate
FROM someTable
WHERE someTable.EndDate =
(SELECT
MAX(t.EndDate)
FROM someTable t)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 02:17:08

SELECT *
FROM tbl t
WHERE EndDate =
(SELECT
MAX(t2.EndDate)
FROM tbl t2 where t.Id = t2.Id
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohdrashid
Starting Member

23 Posts

Posted - 2004-05-07 : 02:54:00

quote:
Originally posted by DustinMichaels

Does this query work for you?

SELECT someTable.ID, someTable.StartDate, someDate.EndDate
FROM someTable
WHERE someTable.EndDate =
(SELECT
MAX(t.EndDate)
FROM someTable t)

thanks for the reply but my apologies i should have indicated the result.
the result should return the records with *. ie the latest endate per id.

Id StartDate EndDate
a 19/03/2001 18/03/2002
a 19/03/2002 18/04/2002*
b 13/08/2000 12/08/2001
b 13/08/2001 12/08/2002
b 13/08/2002 10/07/2002*

mohdrashid
Starting Member

23 Posts

Posted - 2004-05-07 : 03:22:55

quote:
Originally posted by nr

SELECT *
FROM tbl t
WHERE EndDate =
(SELECT
MAX(t2.EndDate)
FROM tbl t2 where t.Id = t2.Id
)
Thanks nr. it worked like a charm.
rashid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kearly2
Starting Member

2 Posts

Posted - 2004-05-26 : 22:02:38

Help! I'm also trying to write a query to find the maximum date in a group of records. I have records of contracts. Here's what I'm using. Can you tell me why it says there is a syntax error and how to fix it? "Contracts" is my table. "Contract Date" is my date field. "Customer Number" is the customer number field.

Contracts.[Contract Date] =
SELECT Contracts.[Contract Date]
FROM Contracts
WHERE Contract Date =
(SELECT MAX(Contracts.[Contract Date]) FROM Contracts WHERE(Contracts.[Customer Number])= (Contracts.[Customer Number]))

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-26 : 22:12:39

1) you are missing [ ] around "Contract Date" in your where clause

2) you are referencing the Contracts table twice; you must give at least one of them an Alias otherwise the sytem doesn't know which reference to the table each expression is refering to. see Nigel's code for an example ("t2" and "t" in his code are aliases)

- Jeff

kearly2
Starting Member

2 Posts

Posted - 2004-05-27 : 02:04:21

Thanks! I've got so far, but if one has two maximum dates that are the same, then is there a way to get it to return both "max-es"? For instance, if I have 1/1/90, 1/2/04, 1/2/04, the query won't run because there are two maximum dates and it thinks it can only return one as the query is written.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources