MYSQL Order before group and then Order again

I have a table with the following fields.

id
uniq
name
text
modified_date

Basically, the id is uniue to each row, but the unique field may be the same as other rows.

What happens is when someone updates information in a form, rather than updating the existing row, it adds a new row which shared the same uniq field value as the original. Each time the user edits the row it loads in the most recently updated row. This allows us to keep a history or previous versions.

What I want to do is retrieve a list of items in the table, sort them by modified_date(desc), group by uniq, and then finally sort the result by name. The final array of results should list the latest modified_date sorted by name.

Just wondering how I would go about doing this? Currently my query is...

SELECT * FROM table GROUP BY unique ORDER BY "modified_date DESC, name ASC"

But obviously the final results are listed by modified date.
SheppardDigitalAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johanntagleConnect With a Mentor Commented:
This should do:

select a.* from table a 
join (select uniq, max(modified_date) max_date from table group by uniq) b on (a.uniq=b.uniq and a.modified_date=b.max_date)
order by a.name;

Open in new window


However, I must say a better way for keeping historical data is store them in a separate table.  This way you don't need to perform the quite heavy SQL above.  You can use triggers to automatically populate the history table for every insert/update of the main table.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
maybe with some sample data it would become clearer to the readers ...

would this article help?
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
Bernard S.CTOCommented:
And as a matter of good practice, don't use select *, but select field1, field2 etc...
0
 
SheppardDigitalAuthor Commented:
Ideally I would have put the data into two tables, however this is a CMS we've already built, and now a client has asked for some unique functionality. It's easier for us to have multiple entries in a single table then it is to split everything in multiple tables, especially since the CMS is already fully functioning.

Regarding the use of SELECT *, we do that because we want every column in the table, which once retrieved from the database it parsed and converted into PHP objects.

I'm not sure which answer to make as the solution, as it seems the first two both resolve the problem using different methods.
0
 
johanntagleCommented:
Not sure which part of the article suggested by angelIII shows the needed solution (no time to read right now), but maybe you can just test which among the two solutions runs faster, then accept that one, and maybe award the other as assisted solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.