How to GET Newest 30 records then SORT THEM ??

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.
LVL 2
RickDaiAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
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
 
RickDaiAuthor Commented:
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
 
jdlambert1Commented:
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
 
snoyes_jwCommented:
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
 
RickDaiAuthor Commented:
4.1 worked. thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.