MySQL Sorting pre-sorted records by timestamp

Question

I'm having a problem with a MySQL query. I've been following: How to select the most recent set of dated records from a mysql table and what I'm trying to do is select the most recient record and sort it by the sales_total field. So first I have to sort by max(timestamp) then sort again by sales_total. I've modified the solution offered above as follows:

$query = "(SELECT * FROM (SELECT * FROM table_name WHERE year =\'2013\' AND category=\'Network\' ORDER BY timestamp DESC) as t1 GROUP BY name) ORDER BY sales_total DESC";
$result = mysql_query($query) or die("Query failed");

This runs perfectly in PHPMyadmin, however If I try and execute the same query in a PHP script I get a Query Failed error. Anyone know why the above seems to work in PHPMyadmin but not in PHP?

My PHP query is as follows:

$query = "(SELECT * FROM (SELECT * FROM table_name WHERE category=\'Network\' ORDER BY timestamp DESC) as t1 GROUP BY name) ORDER BY sales_total DESC";

PHP code shown in edits above. Yes browser (Chrome) really does report Query Failed. — Taffman, Jul 26 '13 at 10:35
@Simon at mso.net Sorry, I have ammended my origional query. So "Fred" may have submitted several sales records over time, But I just want to display Fred's latest Sales figures, and the latest sales figures of each sales person. — Taffman, Jul 26 '13 at 10:37

Gordon Linoff · Answer 1 · 2013-08-01T14:22:40.513

Unfortunately, the post that you are looking at is wrong. MySQL documentation explicitly says that group by chooses arbitrary values for columns when they are not specified in the group by clause.

Here is the explicit quote from here.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

The correct approach (which is standard SQL) is to calculate the appropriate maximum time stamp and then join the results back in:

SELECT t.*
FROM table_name t join
      (select name, year, category, max(timestamp) as maxts
       from table_name
       WHERE year ='2013' AND category='Network' 
       group by name, year, category
      ) tn
      on t.name = tn.name and
         t.year = tn.year and t.category = tn.category and t.timestamp = tn.maxts
ORDER BY sales_total DESC;

EDIT:

This is your query:

SELECT t.*
FROM annualtable t join
     (SELECT year, name, max(countries)
      FROM annualtable
      WHERE name NOT LIKE '%No Entries%' and year <> '0'
      GROUP BY year
      ORDER BY year
     ) tn
     on t.name = tn.name and t.year = tn.year t.countries = tn.countries
ORDER BY year DESC;

There are two problems. One is the missing and before the third clause in the on. The second is that you need to include name in the group by for the subquery. You may have another problem if you expect the order by to do anything. See the above reference in the documentation. So, you may want this:

SELECT t.*
FROM annualtable t join
     (SELECT year, name, max(countries)
      FROM annualtable
      WHERE name NOT LIKE '%No Entries%' and year <> '0'
      GROUP BY year, name
     ) tn
     on t.name = tn.name and t.year = tn.year and t.countries = tn.countries
ORDER BY year DESC;

@Simon at mso.net that fixed it many thanks. The use of '\ and " came from PHP MyAdmin using the Create PHP Code function. Many thanks for this. — Taffman, Jul 26 '13 at 11:00
Thanks for this. Your query also works. Not sure I fully followed the explanation, but I'll be using this as it seems the safer option. thanks to all for taking time out to help. — Taffman, Jul 26 '13 at 11:48
I have a simular problem with another problem. I've tried following Gordon's example but I think I'm doing something wrong here or theres a typo I can't see for looking. My query looks like this: SELECT t.* FROM annualtable t join (SELECT year, name, max(countries) FROM annualtable WHERE name NOT LIKE '%No Entries%' and year <> '0' GROUP BY year ORDER BY year) tn on t.name = tn.name and t.year = tn.year t.countries = tn.countries ORDER BY year DESC; anyone see whats wrong here? — Taffman, Aug 01 '13 at 13:32
Now getting the following PHPAdmin error: "#1054 - Unknown column 'tn.countries' in 'on clause'" — Taffman, Aug 01 '13 at 15:37
ok I've got it working now, thanks. I have to five the max() field and alias and referred to tn. by it's alias. Many thanks. Was going snow blind there for a while ;-) — Taffman, Aug 01 '13 at 15:45
Only problem now is its not displaying just the max(countries) value for each year. It's showing all entries? — Taffman, Aug 01 '13 at 15:48
@Taffman . . . I'm not clear what you really want, but removing `name` from the subquery would get you the max(countries) value. There may be other solutions. If you want, you can phrase this as another question, show the layout of the data and your query. — Gordon Linoff, Aug 01 '13 at 16:05

score 0 · Answer 2 · answered Jul 26 '13 at 10:44

0

Within your PHP code you have \' despite using " quotes. Also you seem to have some superfulous (and possibly error-causing) brackets. Therefore try changing your PHP query string to

$query = "SELECT * 
    FROM (
        SELECT * 
        FROM table_name 
        WHERE category='Network' 
        ORDER BY timestamp DESC
    ) as t1 
    GROUP BY name 
    ORDER BY sales_total DESC";

answered Jul 26 '13 at 10:44

Simon at The Access Group

6,932
22
45

Just shown the next line in my PHP code to show where the Query Failed error comes from. – Taffman Jul 26 '13 at 10:53

MySQL Sorting pre-sorted records by timestamp

2 Answers2