MySQL question on sorting (should be simple)

BrianK · 08-09-2006, 05:02 PM

I have a table that looks like so:

Code:

mysql> select job_name , shot_name , preview_name , date_added from jobs where job_name ='SxxxOct' order by date_added desc;
+----------+-----------------+--------------+---------------------+
| job_name | shot_name       | preview_name | date_added          |
+----------+-----------------+--------------+---------------------+
| SxxxOct  | SO_121_1_24H    | testies1     | 2006-08-09 14:48:47 |
| SxxxOct  | SO_120_hdd4_30H | SO_120_hdd4  | 2006-08-08 19:47:55 |
| SxxxOct  | SO_121_1_24H    | testies      | 2006-08-08 19:30:06 |
| SxxxOct  | SO_121_1_24H    | SO_121_1_24H | 2006-08-08 17:06:25 |
| SxxxOct  | SO_121_hdd2_30H | SO_121_hdd2  | 2006-08-08 17:01:57 |
| SxxxOct  | RnD             | 6            | 2006-08-08 16:58:47 |
| SxxxOct  | RnD             | 5            | 2006-08-08 16:58:02 |
| SxxxOct  | RnD             | 4            | 2006-08-08 16:56:59 |
| SxxxOct  | RnD             | 3            | 2006-08-08 16:56:35 |
| SxxxOct  | RnD             | 2            | 2006-08-08 16:55:09 |
| SxxxOct  | RnD             | 1            | 2006-08-08 16:49:57 |
+----------+-----------------+--------------+---------------------+

I want to print out the unique shot_names, but sort them by the most recent preview associated with that shot. So, what I want is something like looks like this:

Code:

SO_121_1_24H
SO_120_hdd4_30H
SO_121_hdd2_30H
RnD

If I try selecting distinct shot names & ordering by date_added, it doesn't do exactly what I want, I get:

Code:

mysql> select distinct shot_name from jobs where job_name ='SxxxOct' order by date_added desc;
+-----------------+
| shot_name       |
+-----------------+
| SO_120_hdd4_30H |
| SO_121_1_24H    |
| SO_121_hdd2_30H |
| RnD             |
+-----------------+

.. it appears to not be getting the correct date_added - or maybe it's getting the first date that it finds that's attached to whichever field happened to be the unique one, not necessarily the most recent.

How can I sort the shot_names based on the date of the most recent preview added?

xhi · 08-09-2006, 06:22 PM

first thing that crosses my mind is that you should normalize your table a little.. create a job table, a shot table, and a preview table. that would make things alot easier, and correct.

imo

graemef · 08-09-2006, 08:33 PM

As I understand it the Distinct keyword has no guarantee on which row it will select, so sorting on a row that has not been selected will result in problems at some stage.

You have two options:
a) normalise the table
b) use subqueries one that will return the max date for each shot, this will then be used to sort the distinct values

BrianK · 08-09-2006, 09:20 PM

you mean having one column filled with entirely the same data is not a good design?

I see I should have thought this through a bit more clearly from the start.

If anyone has a nice one-liner sql command to get the correct data, please post it. Even though I should, I'll likely not redesign this thing - not worth the time. Otherwise, I'll work on some sort of tiered query.

Thanks for pointing out the problem.