Solved

How to GET Newest 30 records then SORT THEM ??

Posted on 2004-10-01
5
343 Views
Last Modified: 2006-11-17
I am using mySQL as my DB provider.

I want to get the newest products in the table, the field is named: prod_new (DATE type)
my SQL:
"SELECT * FROM prod_detail ORDER BY prod_new DESC LIMIT 30"

This would get the latest items, because it is getting the date in DESC order.
BUt how would I be able to sort these records by the name?

"SELECT * FROM prod_detail ORDER BY prod_new DESC, prod_name ASC LIMIT 30"
I tried above, but as i suspected it orders the date then order the name.

What I want is that it gets the 30 records, and from the 30 records it will reorder them by their name regardless of their date.
0
Comment
Question by:RickDai
[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
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 50 total points
ID: 12204623
If you're using version 4 or better, try this:

SELECT * FROM
(SELECT * FROM prod_detail ORDER BY prod_new DESC LIMIT 30) as s1
ORDER BY prod_name
0
 
LVL 2

Author Comment

by:RickDai
ID: 12204640
That didnt work it gave me an error:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM prod_detail WHERE prod_active = 1 ORDER BY
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12204668
My bad. You've got to have 4.1 or better for the code above to work. I think the only way to do it without upgrading your MySQL is to re-sort it with the programming language you use to fetch the data.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12204864
I haven't encountered a good single-query way to get the top (or bottom) N from a group without using subqueries.  You can use a temporary table - use your existing query to select the records into a new table, then select from that table in the order you want.
0
 
LVL 2

Author Comment

by:RickDai
ID: 12205999
4.1 worked. thanks.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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