SQL question

Going to Cali · 8 Dec 2006 at 15:35

Hi, I have a set of records.

X8973 PACKAGING 1 1.4948 1 06/12/2005 E2640
X8973 PACKAGING 1 1.4948 1 31/10/2001 E2641
09234 PACKAGING 1 8.0187 5 09/12/2004 E2640
09234 PACKAGING 1 8.0187 8 05/05/2004 E2641

Simply, I would like to select records with the most recent dates. Leaving me with

X8973 PACKAGING 1 1.4948 1 06/12/2005 E2640
09234 PACKAGING 1 8.0187 5 09/12/2004 E2640

Can anyone suggest how this is done?

rpstewart · 8 Dec 2006 at 16:19

It depends on the database software your using as the SQL syntax and available commands vary but for DB2 the following would do what you want.

Code:

select * from table order by 6 desc fetch first 2 rows only

Just replace "table" with whatever the table is called and if you want "6" with the name of the date column.

PsychoDuck · 8 Dec 2006 at 17:07

If its SQL2000, try this:-

Code:

SELECT I.Code, I.Description, I.Price, I.Quantity, I.Date, I.Code2
FROM Items I INNER JOIN (
SELECT Code, MAX(Date) LatestDate FROM Items GROUP BY Code
) I2 ON I.Code = I2.Code AND I.Date = I2.LatestDate

rpstewart · 8 Dec 2006 at 17:15

PsychoDuck said:
If its SQL2000, try this:-

Doesn't that give you the latest date for each value of the last column rather than the latest dates recorded in the table? Depends what the OP wants I suppose.

PsychoDuck · 8 Dec 2006 at 20:12

Not Quite Sure What You Mean.

If you use this data:-

X8973 PACKAGING 10.00 100 2005-12-06 00:00:00.000 E2640
X8973 PACKAGING 15.00 200 2001-10-10 00:00:00.000 E2641
09234 PACKAGING 25.00 50 2006-12-01 00:00:00.000 E2640
09234 PACKAGING 30.00 75 2006-12-15 00:00:00.000 E2641

You get this results set after running the query as it uses the code and the latest date to join to itself hence selecting the latest dated row for each code:-

X8973 PACKAGING 10.00 100 2005-12-06 00:00:00.000 E2640
09234 PACKAGING 30.00 75 2006-12-15 00:00:00.000 E2641

Beansprout · 8 Dec 2006 at 21:10

MySQL:

Code:

SELECT foo FROM table ORDER BY thedatecolumn DESC LIMIT 2

Dave M · 9 Dec 2006 at 11:00

MS SQL / Access is similar to mySQL:

SELECT TOP 2 foo FROM table ORDER BY thedatecolumn DESC

PsychoDuck · 9 Dec 2006 at 15:08

Im sure thats not what he wants as there is probably more data in that table than what is listed above. That query just gives you the top 2 latest rows, you will get missing data if there are more than two different stock codes or two dates for a particulay stock code are newer than the dates for any other code.