Solved

MYSQL Order before group and then Order again

Posted on 2013-01-24
5
530 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now