Can GROUPBY clause be used after ORDERBY?

Question

Say I have the following records in the Database

name | address | data
Ann  | CA      | 2020-12-03
Ann  | TX      | 2019-04-06
Ann  | NY      | 2018-09-12

I'm running the following query which produces the "Actual Output"

SELECT name, address, date from mydb
GROUPBY name;

Actual Output

name | address | data
Ann  | NY      | 2018-09-12

How can I get the below "Desired Output"? I thought of doing ORDERBY date first followed by GROUPBY but that syntax is wrong and is not allowed.

# Incorrect Query
SELECT name, address, date from mydb
ORDERBY date ASC GROUPBY name;

Desired Ouput

name | address | data
Ann  | CA      | 2020-12-03

Is there a way to get "Desired Output" using SQL query?

NOTE: The 3 records shown above are for example purpose only and in actual there are n records with different names.

Please someone update the question accordingly. I'm not able to form the short question.

The underlying problem here is that even without `order by`, your original query is already broken and only allowed in a MySQL-specific exception mode (e.g. every other database system would give you an error, because it doesn't make any sense). See e.g. [What are the benefits of only_full_group_by mode?](https://stackoverflow.com/q/45484068). You are looking for a "groupwise maximum" (e.g. content of the row that has the highest date), see e.g. [Groupwise maximum](https://stackoverflow.com/q/15211479) — Solarflare, Sep 20 '20 at 08:47
use this: { SELECT T1.name, T1.address, T1.date from mydb as T1 join (select name, MIN( date ) as MDate from mydb group by name) as T2 on T1.name = T2.name and T1.date = T2.MDate } — Sahar Rezazadeh, Sep 20 '20 at 09:02

0 Answers0