[Resolved] Additional sorting of query

ywickham

I have the following query which pulls the result set I need:

SELECT manufacturer_name, model_name, review_url
FROM camera_general
WHERE review_url is not null
ORDER BY review_date DESC
LIMIT 50;

The most important thing is that they be the latest reviews, so the query has to return those with the latest review_date first, which it does. Yet, I need to further sort the list by manufacturer_name and then model_name.

Is there anyway I can do that within the query itself, or will I have to write code to sort the result set I receive?

Thanks!

Sxooter

SELECT manufacturer_name, model_name, review_url
FROM camera_general
WHERE review_url is not null
ORDER BY 
  review_date DESC,
  manufacturer_name ASC,
  model_name ASC
LIMIT 50;

ywickham

Thank you Sxooter, but it looks like I need to clarify my problem more.

If I add "manufacturer_name, model_name" to the "order by" line per your suggestion, I do get the latest 50 entries, but they are sorted by review_date first and then within the review_date, by manufacturer_name and model_name.

What I need from the query is for it to return the latest 50 digital cameras sorted by manufacturer_name and model_name. They way for me to tell that an entry in the database is the latest entry is by the review_date, which is why I am currently sorting on the review_date and then cutting off the list at the 50th record.

How can I modify my select statement so that it returns the latest 50 entries sorted by manufacturer_name and model_name?

Thanks!

Sxooter

Just wrap it in a subselect:

select * from
(SELECT manufacturer_name, model_name, review_url
FROM camera_general
WHERE review_url is not null
ORDER BY
review_date DESC,
LIMIT 50) as sub
order by sub.manufacturer_name ASC,
sub.model_name ASC;

ywickham

A subquery is not an option for me, as support for them starts with version 4.1, which we are not running.

Any other options?

Sxooter

Just select it into a temporary table then select back out from there with an order by.

Since temporary tables are (should be??? I don't know MySQL all that well) only visible within a session to that session, you shouldn't have to worry about having the same page run twice and hitting the same temporary table as each other.

ywickham

Thank you. After much work on an inner join statement offered by someone else, it seems that your suggestion is the best solution.

I will implement as follows:

CREATE TEMPORARY TABLE camera_general_temp
SELECT manufacturer_name, model_name, review_url
FROM camera_general
WHERE review_date IS NOT NULL
ORDER BY review_date DESC
LIMIT 50;

SELECT manufacturer_name, model_name, review_url
FROM camera_general_temp
ORDER BY manufacturer_name, model_name;

Thank you again.

Sxooter

The nice thing here is that MySQL usually flies at temporary table work, especially on small sets, so while it seems more complex to use a temporary table, the history MySQL has of using temp tables in the past to get around subselects means that the optimization on using them plays in your favor.

ywickham

I actually hadn't realized that there was such a thing as a temporary table and it took a bit of searching for me to find more information on it. It did sound complicated at first, but it seems to be the best solution for what I need until we move from running 3.23 to 4.1.

Thanks again.