CODEWITHSUNDEEP
mysqlsqlmariadb-10.3
draw134
draw134

Reputation: 1187

MySQL select orderby date returns duplicate values

I want to get every user and the last payment they do. I got 2 tables here the users and finances. I tried to add groupby and I got the result I want but, it gets the oldest record in the other table. Does anyone know how could I achieve this?

My first query

SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.date 
from users 
JOIN finances on users.id = finances.user_id 
JOIN schoolyears on users.school_id = schoolyears.school_id 
ORDER BY finances.date DESC;

Result I got

+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+
| name            | email                        | phone              | parent_id | section_id | amount | description  | name         | date       |
+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+
| Madelynn Stokes | [email protected] | +63 (971) 659-8143 |        10 |       NULL | 1000   | New Payables | SY-2019-2020 | 2019-11-14 |
| Annamarie Morar | [email protected]          | (0997) 212-7919    |         3 |       NULL | 500    | New Pays     | SY-2019-2020 | 2019-11-14 |
| Madelynn Stokes | [email protected] | +63 (971) 659-8143 |        10 |       NULL | 5000   | Old Payables | SY-2019-2020 | 2019-11-13 |
| Annamarie Morar | [email protected]          | (0997) 212-7919    |         3 |       NULL | 200    | Old Pays     | SY-2019-2020 | 2019-11-13 |
+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+

I want only the newest record in other table. the New payables and New pays.

I tried this second query

SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.date 
from users 
JOIN finances on users.id = finances.user_id 
JOIN schoolyears on users.school_id = schoolyears.school_id 
GROUP BY users.id 
ORDER BY finances.date DESC;

It works but I got the oldest record. 

+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+
| name            | email                        | phone              | parent_id | section_id | amount | description  | name         | date       |
+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+
| Madelynn Stokes | [email protected] | +63 (971) 659-8143 |        10 |       NULL | 5000   | Old Payables | SY-2019-2020 | 2019-11-13 |
| Annamarie Morar | [email protected]          | (0997) 212-7919    |         3 |       NULL | 200    | Old Pays     | SY-2019-2020 | 2019-11-13 |
+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+

Upvotes: 1

Views: 81

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below -

DEMO

SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.dateval 
from users 
JOIN finances on users.id = finances.user_id 
JOIN schoolyears on users.school_id = schoolyears.school_id 
where finances.dateval=
(select max(dateval) from finances f where finances.user_id=f.user_id)

Upvotes: 2

Related Questions