Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MYSQL Order before group and then Order again

Posted on 2013-01-24
5
Medium Priority
?
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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