Help creating a query

[RicardoBett]

Hello guys,

 

I am having trouble solving the following problem.

 

Raw data table

ID.  NAME.  TIMESTAMP.

1.    Tim.       14:00

2.    Eve.       15:00

3.    Tim.       15:10

4.    Tim.       15:30

5.     Eve.       16:00

 

 

Expected result after query

ID.  NAME.  TIMESTAMP.

5.    Eve.      16:00

2.    Eve.      15:00

4.    Tim.      15:30

3.    Tim.      15:10

1.     Tim.      14:00

 

So the objective is to get most recent row order by TimeStamp but group the results with name.

[Ingolme]

The simplest solution would be to order first by name and then by time. [ ORDER BY NAME, TIMESTAMP DESC ]

For your example data set the result would be identical to your expected result.

It would get vastly more complicated if you want to order the names based on the timestamps attached to them.

 

[RicardoBett]

Thank you for your reply. 

 

But suppose that we insert new record "Tim" at 18:00.

 

Now I want to show Tim first because he has the most recent timestamp. And only after showing all Tim records the. Show Eve

[Ingolme]

As I mentioned before, it's complicated.

You'll first have to find the highest timestamp for each name, then attach this timestamp to all rows which have the name. Finally, you would sort by this timestamp first, then the other timestamp second. It most likely involves nested queries which are very slow, but there might be a more efficient method I'm not thinking of right now.

This query might work. I wasn't able to test this because it looks like W3Schools' SQL editor doesn't allow me to experiment with nested queries. It might have a syntax error or result in duplicate entries.

SELECT t1.ID, t1.NAME, t1.TIMESTAMP AS main_timestamp, t2.TIMESTAMP AS sorting_timestamp
FROM table AS t1
JOIN table AS t2 ON (
  t1.NAME = t2.NAME AND
  t2.TIMESTAMP IN (SELECT MAX(TIMESTAMP) FROM table WHERE NAME = t1.NAME LIMIT 1)
)
ORDER BY sorting_timestamp, main_timestamp DESC

I usually handle complex problems of this sort with the aid of a server-side programming language. Either doing two independent queries and using the results of one query as inputs to the next, or by just sorting the results manually after retrieving them from the database.

 

[RicardoBett]

Hello, thank you for your help.🙂 Unfortunately i couldn't make it work either.