returning the rec with the latest date

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)

returning the rec with the latest date

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-10-17 : 18:01:15

Here is what my data looks like. I want to write a query that would return only the 2nd and 3rd record. The 1st and 2nd records have the same file name, but I only want one that has the latest date.

ICN txtfileName DateCreated Cycledate
1204 10142003_213600184.pdf 2003-10-17 15:04:17.557 2003-10-14
1204 10142003_213600184.pdf 2003-10-18 00:00:00.000 2003-10-14
1204 10142003_213601447.pdf 2003-10-17 15:11:26.330 2003-10-14

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-17 : 18:18:13

SELECT txtfileName, MAX(DateCreated) AS maxDateCreated

FROM MyTable

GROUP BY txtfileName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-17 : 18:19:34

SELECT t1.ICN, t1.txtfileName, t1.DateCreated, t1.Cycledate
FROM Table1 t1
WHERE t1.DateCreated = (SELECT MAX(t2.DateCreated) FROM Table1 t2 WHERE t1.txtfileName = t2.txtfileName)

It helps if you would provide DDL and DML statements. This is how you should post your information for us:

SET NOCOUNT ON

CREATE TABLE Table1
(
Col1 INT NOT NULL,
Col2 VARCHAR(50) NOT NULL,
Datett DATETIME NOT NULL
)

INSERT INTO Table1 VALUES (1, 'test1.txt', '06/07/2003 00:00')
INSERT INTO Table1 VALUES (1, 'test1.txt', '06/07/2003 12:00')
INSERT INTO Table1 VALUES (2, 'test2.txt', '06/08/2003 00:00')
INSERT INTO Table1 VALUES (2, 'test2.txt', '06/09/2003 13:00')
INSERT INTO Table1 VALUES (2, 'test2.txt', '07/08/2003 00:00')
INSERT INTO Table1 VALUES (2, 'test2.txt', '06/08/2003 01:00')
INSERT INTO Table1 VALUES (2, 'test3.txt', '06/08/2003 00:00')

DROP TABLE Table1

We use the code and fill in the gap between the INSERT and DROP. Otherwise, we have to do a bunch of typing. Your question maybe ignored if you don't provide the information.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources