• Registered: 2006-05-11
  • Posts: 8

Topic: help for a SQL request

Hello,

Sorry to trouble you but I just cannot find a solution for a pretty straightforward SQL request I want to add in extern.php

I want to have a the last records for a set of records.

if I have the following table

idx   relate_idx    creation
1          2             3
2          3             2
3          2             1
4          4             4
5          2             5

I would like to sort my request according to the time AND to relate_idx but with only the last record (according to the time) in the relate_idx series.

so that the result would be

idx   relate_idx    creation
5          2              5
4          4              4
2          3              2

Hope this is clear. Once again sorry if this sounds basic to you but I just cannot find out how to make the SQL request.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: help for a SQL request

Moved to Programming

  • Registered: 2007-01-19
  • Posts: 24

Re: help for a SQL request

Actually, I figured out a way to do this with one query (this won't work on MySQL 4.0.X or earlier, though). I tried it out myself on your test data and the result is exactly what you wanted.

SELECT idx, relate_idx, creation FROM table WHERE (relate_idx, creation) IN (SELECT relate_idx, max(creation) FROM table GROUP BY relate_idx) ORDER BY creation DESC;