Jump to content

Help creating a query


RicardoBett
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...