selecting most current record with one unique column

[douglevin]

I have a table that has 3 columns: filePath, filePrefix, timestamp.

I need a query that will return the most current filePath of records with a unique filePrefix.

For example, say there are four records:

1) filePathA, filePrefixA, 10:00
2) filePathB, filePrefixA, 11:00
3) filePathC, filePrefixB, 10:00
4) filePathD, filePrefixB, 11:00

I need the 2nd and 4th rows returned.
Any help is much appreciated.

[Atli]

Hi Doug. Welcome to The Scripts!

You could try something like this:
[code=mysql]
SELECT filePath FROM myTbl
ORDER BY time_stamp
GROUP BY filePrefix
[/code]

[douglevin]

Thanks for the reply, but I'm getting an error when I run that query.

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY filePrefix"

Any other ideas

[Atli]

O yea, try switching the GROUP BY and the ORDER BY clauses.
And make sure you change 'time_stamp' to 'timestamp' or whatever you used in your table. I changed that because 'timestamp' is a reserved keyword that would have looked weird in the sample I posted.
[code=mysql]
SELECT filePath FROM myTbl
GROUP BY filePrefix
ORDER BY time_stamp
[/code]

[douglevin]

This query ran correctly, however, it returned rows 1 and 3, as opposed to rows 2 and 4 from the table in the original post. I need the record with the most recent timestamp for each group of filePrefix

Thanks again.

[Atli]

Add the keyword ASC to the ORDER BY clause.

ASC is short for Ascending and DESC for Descending.

You may want to check out Sorting Rows in the MySQL Reference Manual.

You can also see a complete definition for the SELECT clause and all of it's extra clauses in the MySQL Reference Manual.

[douglevin]

I had already tried adding the 'ASC/DESC' keywords, but it does not change the result set. As I understand it, the order by clause will order the result set by timestamp, but as I am receiving the incorrect result set, this does not help.
I have spent hours trying to obtain the correct solution, and I posted here as a last resort, not a first option.

I do appreciate your feedback, any more ideas?

[Atli]

OK, thats weird. I was sure that would work :/
It seems like we do not get a say in how the GROUP BY clause sorts the rows, which is kind of odd.

The simplest way I can see to get around this is to re-arrange the table using a sub-query before we call the GROUP BY clause:
[code=mysql]
SELECT filePath FROM (
SELECT * FROM myTbl
ORDER BY time_stamp ASC
) AS tbl
GROUP BY filePrefix
[/code]

[douglevin]

Bingo, almost. Changing 'ASC' to 'DESC' returned the correct result set.
Thanks, so much.