Solved

MYSQL Order before group and then Order again

Posted on 2013-01-24
5
531 Views
Last Modified: 2013-01-28
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.
0
Comment
Question by:SheppardDigital
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38815143
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38818295
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
 
LVL 29

Expert Comment

by:fibo
ID: 38824421
And as a matter of good practice, don't use select *, but select field1, field2 etc...
0
 

Author Comment

by:SheppardDigital
ID: 38826013
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 38826028
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question