mysql group by, two conditions, limit 1

156
March 05, 2018, at 02:38 AM

I would like to select the oldest event date for all tickets. I created this query "tixdate" view in mysql (that I use for other purposes) and it sorts the records by ticket number, then by eventdate. This view looks like this:

EventDate  | Ticket                                    
2018-02-25 | 998<br>
2018-02-25 | 998<br>
2018-02-25 | 998<br>
2018-02-11 | 998<br>
2018-02-11 | 998<br>
2018-02-04 | 998<br>
2018-02-04 | 998<br>
2018-02-03 | 998<br>
2018-02-03 | 998<br>
2018-01-27 | 998<br>
2018-01-27 | 998<br>
2018-01-21 | 998<br>
2018-01-21 | 998<br>
2018-01-14 | 998<br>
2018-01-14 | 998<br>
2018-01-13 | 998<br>
2018-01-13 | 998<br>
2018-01-07 | 998<br>
-----------------------------------

I created a new mysql view to list only the oldest event date:

    SELECT
     'audTix` AS `audTix`,
     `audDt` AS `audDt`
    FROM
     `tixdate`
    GROUP BY
     `audTix`
    ORDER BY
     `audDt` DESC

which produced one record:

2018-01-13 | 998

But it should have selected the 2018-02-25 date. What am I doing wrong?

Answer 1

No matter whether you select from a table or a view, the data is considered unordered. So even though your view tixdate contains an ORDER BY clause, this gets ignored once you select from it. (More precise: the DBMS is free to ignore it; and it should do so in order to select data more quickly.)

Any way, even if this were not the case, then your query still wouldn't work. You group by audTix, so you get one result row per audTix. But you select audDt which is an audDt belonging to the audTix. You don't say which (e.g. the minimum, maximum or average), so MySQL translates this to ANY_VALUE(audDt). You then sort the results by audDt, but as these are arbitrarily picked, this makes no sense. (And if your table only contains one audTix, you only get one result row, so there is nothing to sort obviously.)

What you want is the maximum date, so select it:

SELECT audTix, MAX(audDt)
FROM tixdate
GROUP BY audTix
ORDER BY audTix;

If you want to order by date, then that's ORDER BY MAX(audDt) instead. If you only want the one audTix with the latest date, you'd ORDER BY MAX(audDt) DESC LIMIT 1.

And if you only want the oldest event date from all records (without selecting the associated audTix), that's

SELECT MAX(audDt) FROM tixdate;

of course.

(And you can remove the ORDER BY clause from your view tixdate. As mentioned, an ORDER BY clause in a view makes no sense.)

Rent Charter Buses Company
READ ALSO
WP_users joining WP_usermeta from MS access

WP_users joining WP_usermeta from MS access

I need to make a request from MS_Access 2013 where I join two tables in a WordPress mysql database

151
Dropdown with values from database query that is generated using a previous dropdown value that is selected instantly [on hold]

Dropdown with values from database query that is generated using a previous dropdown value that is selected instantly [on hold]

Dropdown with values from database query that is generated using a previous dropdown value that is selected instantly

111
RMySQL Database Connection Bad Handshake

RMySQL Database Connection Bad Handshake

I'm trying to connect to a remote database using RMySQL using SSL, and cannot seem to get it to workI'm on OSX, R version 3

317
MySQL Granting permissions on new database

MySQL Granting permissions on new database

I have three DB accounts:

215