Solved

How to GET Newest 30 records then SORT THEM ??

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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