LEFT JOIN to return only first row

Question

I am making a join with two tables, tab_usuarios (users) and tab_enderecos (address).

tab_usuarios structure:

id_usuario	nome	usuario
1	Administrador	admin
2	Novo Usuário	teste
3	Joao Silva	jao

tab_enderecos structure:

id_endereco	id_usuario	cidade	uf
2	1	cidade	SP
20	2	Lorena	SP
22	2	Lorena	SP
24	3	Campinas	SP
28	4	Lorena	SP

I have this simple query which brings me the following result:

Select
    u.id_usuario,
    u.usuario,
    u.nome,
    e.id_endereco,
    e.cidade,
    e.uf
From
    tab_usuarios u Left Join
    tab_enderecos e On u.id_usuario = e.id_usuario

id_usuario	usuario	nome	id_endereco	cidade	uf
1	admin	Administrador	2	cidade	SP
2	user 2	Novo Usuário	22	Lorena	SP
2	user 2	Novo Usuário	20	Lorena	SP
3	jao	Joao Silva	24	Campinas	SP
4	teste	fabio	28	Lorena	SP

What I want is, for example, for id_usuario = 2, I only want to bring the id_endereco = 20, which is the first address that have been inserted on the database.

I tried with min and a couple others.

Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — philipxy, May 13 '22 at 16:33
Which version of Myql? 8.0.14 and later have a lateral join that can make this much easier. The other option is a windowing function. — Joel Coehoorn, May 13 '22 at 17:02
"... id_endereco = 20, which is the first address that have been inserted on the database..." -- how do you know it's the **first** one? The question does not make sense as asked. Voting to close. — The Impaler, May 13 '22 at 17:27
because id_endereco is primary key with auto increment so the first is the min number — Fabio Gomes, May 13 '22 at 17:40

Joel Coehoorn · Accepted Answer · 2022-05-13T17:15:52.297

This should do it, assuming you have MySql 8.0 and not some ancient 5.x version:

SELECT * 
FROM (
    SELECT  u.id_usuario, u.usuario, u.nome, e.id_endereco, e.cidade, e.uf,
        row_number() over (partition by u.id_usuario order by e.id_endereco) rn
    FROM tab_usuarios u 
    LEFT JOIN tab_enderecos e On u.id_usuario = e.id_usuario
) t
WHERE rn = 1

See it work here:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c506baf8157f82390bb335d074e7614c

LEFT JOIN to return only first row

1 Answers1