0

table1:

esps
--------------
A
B
C
--------------

table2:

id      _name       esp
------------------------------
1       Ana        A
2       Ana        B
3       Mario      A
4       Jorge      A
5       Jorge      C
6       Ana        A
------------------------------

My query:

SELECT * FROM table2 where esp = 'A' group by _name having id = max(id);

Query result:

id      _name      esp
-----------------------------
3       Mario      A
4       Jorge      A
-----------------------------

Expected result:

id      _name      esp
-----------------------------
6       Ana        A
3       Mario      A
4       Jorge      A
-----------------------------

Any advice or sugestion will be appreciated. Mysql version 8.0.16

1
  • Specify MySQL version.
    – Akina
    Aug 16, 2019 at 19:32

3 Answers 3

0

If you want the 'last' A:

SELECT * FROM table2
    where esp = 'A'
    ORDER BY id DESC
    LIMIT 1;

If you need something more complicated, see "groupwise-max".

0
WITH cte AS (SELECT *, 
                    ROW_NUMBER() OVER (PARTITION BY name 
                                       ORDER BY id DESC) rn 
             FROM table2 
             WHERE esp='A' )
SELECT *
FROM cte
WHERE rn=1
-1

Maybe that will work for you?

SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc Limit 1

OR

SELECT * FROM table2 where id = (SELECT max(id) FROM table2 where esp = 'A' group by _name)

EDIT This should work

SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc
1
  • I tried your query, but 'Ana's id is 1 instead of the correct 6. Thanks anyway Aug 16, 2019 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.