CODEWITHSUNDEEP
postgresqljoinmax
Smok
Smok

Reputation: 117

SQL MAX join query returns multiple rows

I wrote a small select query, which is a part of larger one that I use to retrieve items with their additional properties from few tables in PostgreSQL DB. 

SELECT 
  isni.inventory_id_inventory,
  max(isni.issue_note_id_issue_notes) AS latest_issue_note,
  isn.direction
FROM issue_note_items isni
JOIN issue_notes isn ON isni.issue_note_id_issue_notes = isn.id
GROUP BY isni.inventory_id_inventory, isn.direction

All I need is to get one record for each of table with inventory_id_inventory key, with highest issue_note_id_issue_notes value. Or other words, i would like to find one, last issue note with it's direction for each of the distinct inventory id.

What I get is one or two rows - if there are two diffrent directions (there are two small int values possible in this column). Seems easy, should be fixed using left join, but it does'nt work as expected probably becouse of GROUP BY clause. What should I do to make it work as expected?

Tables and example data:
issue_notes 

ID  direction
1   1
2   2
3   2
4   1
5   2
6   1

issue_note_items 

ID  inventory_id_inventory  issue_note_id_issue_notes
1   12                      1
2   123                     1
3   12                      2
4   12                      4
5   35                      4
6   123                     5
7   35                      6

Expected output:

ID  inventory_id_inventory  issue_note_id_issue_notes  direction
4   12                      4                          1
6   123                     5                          2
7   35                      6                          1

Thank You very much for any help

Upvotes: 1

Views: 1001

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131237

Rows in a relational database have no order. There's no "last" entry unless something specifies an order.

In this case it appears the order is by ascending inventory_id_inventory for each inventory_id_inventory. Given that order, one can use the ROW_NUMBER() ranking function to calculate a rank for each result of the join , eg :

SELECT 
  isni.id,
  isni.inventory_id_inventory,
  isni.issue_note_id_issue_notes,
  isn.direction,
  ROW_NUMBER() OVER(partition by inventory_id_inventory 
                    order by issue_note_id_issue_notes desc) as rn
FROM issue_note_items isni
JOIN issue_notes isn ON isni.issue_note_id_issue_notes = isn.issue_note_id

partition by inventory_id_inventory means we want a separate ranking per partition by inventory_id_inventory. order by issue_note_id_issue_notes desc will return 1 for the largest ID, making it easier to filter for the "latest" entry.

It's not possible to refer to the rn function in the where clause. This can be fixed by using a CTE :

with notes as (
    SELECT 
      isni.id,
      isni.inventory_id_inventory,
      isni.issue_note_id_issue_notes,
      isn.direction,
      ROW_NUMBER() OVER( partition by inventory_id_inventory 
                         order by issue_note_id_issue_notes desc) as rn
    FROM issue_note_items isni
    JOIN issue_notes isn ON isni.issue_note_id_issue_notes = isn.issue_note_id      
)
select * 
from notes
where rn=1
order by issue_note_id_issue_notes

Using the following data :

create table issue_notes (issue_note_id int,direction int);
create table issue_note_items (id int, inventory_id_inventory  int,issue_note_id_issue_notes int);

insert into issue_notes
values
(1,1),
(2,2),
(3,2),
(4,1),
(5,2),
(6,1);

insert into issue_note_items 
values
(1,12 ,1),
(2,123,1),
(3,12 ,2),
(4,12 ,4),
(5,35 ,4),
(6,123,5),
(7,35 ,6);

The result will be :

|id |inventory_id_inventory |issue_note_id_issue_notes  |direction  |rn
|4  |12                     |4                          |1          |1
|6  |123                    |5                          |2          |1
|7  |35                     |6                          |1          |1

SQL Fiddle here

Upvotes: 2

Igor Cova
Igor Cova

Reputation: 3514

I wrote something in my project, it may look like this 

SELECT 
  isni.inventory_id_inventory,
  isni.issue_note_id_issue_notes AS latest_issue_note,
  isn.direction
FROM issue_notes isn 
left join lateral (
  select 
    isni.inventory_id_inventory,
    isni.issue_note_id_issue_notes
  from issue_note_items isni
  where isni.issue_note_id_issue_notes = isn.issue_note_id
  order by isni.issue_note_id_issue_notes desc
  limit 1      
) as isni on true

Upvotes: 0

Related Questions