MariaDB Select Distinct ON (An Expression was Expected (Near ON)-CodePudding

I am trying to do a simple query using Distinct On a Column (As i still want to return all other columns in the table) But when i attempt to use the ON (Column) clause it keeps displaying an error "An Expression was expected (Near ON)"

I have no idea what this message means, as the query appears to be formatted correctly, is this something specific with MariaDB instead of MySQL?

MariaDB version 10.3.28 If this is of any use?

SELECT DISTINCT ON (No, Branch) *
FROM   Table1
ORDER  BY No, a DESC;

Table1

|    ID     |     No     |    Branch     |     Datetime     |

| --------- | ---------- | ------------- | ---------------- |

| 1         | 1          |  1            | 18/10/2021 10:00 |
| 2         | 1          |  1            | 19/10/2021 10:00 |
| 3         | 1          |  2            | 22/10/2021 10:00 |
| 4         | 1          |  2            | 20/10/2021 11:37 |
| 5         | 1          |  1            | 21/10/2021 10:00 |
| 6         | 1          |  1            | 22/10/2021 11:37 |
| 7         | 2          |  1            | 20/10/2021 10:00 |
| 8         | 2          |  1            | 22/10/2021 11:37 |

Basically i was wanting to use Distinct on "No" and "Branch" to essentially give me 1 record where No and Branch are unique and its using the MAX(Datetime)

I have attempted to use Group by clause, but this gives me a row with mixed results from other rows.

The results i am after would be:

|    ID     |     No     |    Branch     |     Datetime     | 
| --------- | ---------- | ------------- | ---------------- |
| 6         | 1          |  1            | 22/10/2021 11:37 |
| 3         | 1          |  2            | 22/10/2021 10:00 |
| 8         | 2          |  1            | 22/10/2021 11:37 |

I would be wanting to to display all columns in the table, as i will also be doing a Join to fetch the Name based on No and Branch

CodePudding user response：

Try:

CREATE TABLE table_tst (
  `ID` int(5),
  `No` int(5), 
  `Branch` int(5), 
  `Datetime` Datetime
);
    
INSERT INTO table_tst VALUES 
(1,1,1,'2021-10-18 10:00:00'),
(2,1,1,'2021-10-19 10:00:00'),
(3,1,2,'2021-10-22 10:00:00'),
(4,1,2,'2021-10-20 11:37:00'),
(5,1,1,'2021-10-21 10:00:00'),
(6,1,1,'2021-10-22 11:37:00'),
(7,2,1,'2021-10-20 10:00:00'),
(8,2,1,'2021-10-22 11:37:00');


   select * 
from table_tst where (No,Branch,Datetime) in 
(
select `No`,`Branch`,MAX(`Datetime`)  as max_time
from table_tst
group by `No`,`Branch`
)
order by No ,Branch ASC;

Demo: https://www.db-fiddle.com/f/vhqJXYFy52xRtVBc97R1EL/8

datetime is a reserved word in mysql : https://dev.mysql.com/doc/refman/8.0/en/keywords.html and it should be in backticks or I suggest using other non reserved words