Solved

MYSQL Order before group and then Order again

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 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