Skip to content

Tag: greatest-n-per-group

LEFT JOIN to return only first row

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

UNION ALL Slower than N queries

This question is following this question where I wanted to select the MAX value of multiples fields while retrieving each row. The accepted answer with UNION ALL worked like a charm but I now have some scaling issues. To give some context, I have more than 3 million rows in my matches table and the filters used in the WHERE

Get last and first record using rank()

I need to get first and last record (ordered by Date column) from table for certain SSID. It is not a problem if there is more records with same max or min date. All I need is union all. I am getting last record having max(date) with: How to I get first record (min(Date)) as well (same thing only with

Select the row with the most recent modified date

I have this table: user_id name email modified_date 1 John 2022-01-01 1 John 2022-01-02 1 Lucy 2022-01-03 2 Joey 2021-12-24 3 Mike 2022-01-01 3 Mary 2022-01-02 I’m trying to get unique user_id’s email with the most recent modified_date. This is my expected output: user_id email name 1 Lucy 2 Joey 3