MySQL Selecting wrong column value in Group By query with alias in where clause

Question

I know this question was already asked in a similar way, but I could not found any with an alias in the where clause.

I have a table structure like this:

CREATE TABLE Orders
( ID int NOT NULL Primary Key
, OrderNr VARCHAR(6) NOT NULL
, Date DATE NOT NULL
, Time CHAR(6) NOT NULL
, GeoCode CHAR(6) NULL) ;

My insert looks like this:

INSERT INTO orders (ID, OrderNr, Date, Time, GeoCode) VALUES (1, '123456', '2022-02- 
15', '111110', '4022')
, (2, '123457', '2022-02-15', '121210', '4022')
, (3, '123455', '2021-04-15', '171515', '4020')
, (4, '123455', '2021-04-16', '150302', '4022')
, (5, '123466', '2022-03-03', '191810', '4020')
, (6, '123466', '2022-03-04', '121410', '4022')

Now I´m trying to get the latest Date and Time values for all OrderNr like this:

SELECT ID, OrderNr, MAX(cast(concat(Date, ' ', cast(Time as Time)) as datetime)) as 
DateAndTime, GeoCode
FROM Orders o1
GROUP BY OrderNr

The Results shows the right latest date and time but the GeoCode is wrong. E.g for the OrderNr 123455 it is 4020 but should be 4022.

I know that similar question were already asked but I cant use the alias in the where clause. Can somebody explain to me what I´m doing wrong? Thank you very much in advance.

Answer 1

If your mysql version support ROW_NUMBER window function you can try this

SELECT *
FROM (

    SELECT ID, 
          OrderNr, 
          cast(concat(Date, ' ', cast(Time as Time)) as datetime) DateAndTime, 
          GeoCode,
          ROW_NUMBER() OVER(PARTITION BY OrderNr ORDER BY cast(concat(Date, ' ', cast(Time as Time)) as datetime) DESC) rn
    FROM Orders o1
) t1
WHERE rn = 1

or use subquery with EXISTS

SELECT *
FROM Orders o1
WHERE EXISTS (
    SELECT 1
    FROM Orders oo
    WHERE oo.OrderNr = o1.OrderNr
    HAVING MAX(oo.Date) = o1.Date
)

sqlfiddle