Solved

How to GET Newest 30 records then SORT THEM ??

Posted on 2004-10-01
5
338 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

20 Experts available now in Live!

Get 1:1 Help Now