Limit join to one row

Question

I have the following query:

SELECT sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount, 'rma' as     
    "creditType", "Clients"."company" as "client", "Clients".id as "ClientId", "Rmas".* 
FROM "Rmas" JOIN "EsnsRmas" on("EsnsRmas"."RmaId" = "Rmas"."id") 
    JOIN "Esns" on ("Esns".id = "EsnsRmas"."EsnId") 
    JOIN "EsnsSalesOrderItems" on("EsnsSalesOrderItems"."EsnId" = "Esns"."id" ) 
    JOIN "SalesOrderItems" on("SalesOrderItems"."id" = "EsnsSalesOrderItems"."SalesOrderItemId") 
    JOIN "Clients" on("Clients"."id" = "Rmas"."ClientId" )
WHERE "Rmas"."credited"=false AND "Rmas"."verifyStatus" IS NOT null 
GROUP BY "Clients".id, "Rmas".id;

The problem is that the table "EsnsSalesOrderItems" can have the same EsnId in different entries. I want to restrict the query to only pull the last entry in "EsnsSalesOrderItems" that has the same "EsnId".

By "last" entry I mean the following:

The one that appears last in the table "EsnsSalesOrderItems". So for example if "EsnsSalesOrderItems" has two entries with "EsnId" = 6 and "createdAt" = '2012-06-19' and '2012-07-19' respectively it should only give me the entry from '2012-07-19'.

score 17 · Accepted Answer · edited May 23 '17 at 12:24

17

SELECT (count(*) * sum(s."price")) AS amount
     , 'rma'       AS "creditType"
     , c."company" AS "client"
     , c.id        AS "ClientId"
     , r.* 
FROM   "Rmas"            r
JOIN   "EsnsRmas"        er ON er."RmaId" = r."id"
JOIN   "Esns"            e  ON e.id = er."EsnId"
JOIN  (
   SELECT DISTINCT ON ("EsnId") *
   FROM   "EsnsSalesOrderItems"
   ORDER  BY "EsnId", "createdAt" DESC
   )                     es ON es."EsnId" = e."id"
JOIN   "SalesOrderItems" s  ON s."id" = es."SalesOrderItemId"
JOIN   "Clients"         c  ON c."id" = r."ClientId"
WHERE  r."credited" = FALSE
AND    r."verifyStatus" IS NOT NULL 
GROUP  BY c.id, r.id;

Your query in the question has an illegal aggregate over another aggregate:

sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount

Simplified and converted to legal syntax:

(count(*) * sum(s."price")) AS amount

But do you really want to multiply with the count per group?

I retrieve the the single row per group in "EsnsSalesOrderItems" with DISTINCT ON. Detailed explanation:

Select first row in each GROUP BY group?

I also added table aliases and formatting to make the query easier to parse for human eyes. If you could avoid camel case you could get rid of all the double quotes clouding the view.

edited May 23 '17 at 12:24

Community

1
1

answered Sep 29 '12 at 13:16

Erwin Brandstetter

605,456
145
1,078
1,228

im not looking for the last esn (i.e. the one with the highest id). I am looking for the last "EsnsSalesOrderItems" entry with that specific "EsnId" – user1175817 Sep 29 '12 at 13:26
@user1175817: Of course with a specific `"EsnId"`, but among those with this specific ID, which are you going to pick? Define "last one". – Erwin Brandstetter Sep 29 '12 at 13:27
The one that appears last in the table "EsnsSalesOrderItems". So for example if "EsnsSalesOrderItems" has two entries with the EsnId=6 and createdAt = 6-19-2012 and 7-19-2012 respectively it should only give me the entry from 7-19-2012 – user1175817 Sep 29 '12 at 13:30
@user1175817: I use that in my updated answer. This information should go into the question to make it complete. – Erwin Brandstetter Sep 29 '12 at 13:35
@user1175817: Yes, I think I understand now. Add the information about "createdAt" defining the "last row" *to the question above* to make it clear. – Erwin Brandstetter Sep 29 '12 at 13:37
Brandstetter: Thanks for the tip on the aliases. and the answer is correct now for the most part. The only error is removing the initial "necessarily inefficient" select item. My original select item along with your join statement gives the correct calculation for amount. Your select statement doubled the amount for some reason. – user1175817 Sep 29 '12 at 13:42

score 5 · Answer 2 · 2012-09-29T14:59:31.270

5

Something like:

join (
  select "EsnId", 
         row_number() over (partition by "EsnId" order by "createdAt" desc) as rn
  from "EsnsSalesOrderItems"
) t ON t."EsnId" = "Esns"."id" and rn = 1

this will select the latest "EsnId" from "EsnsSalesOrderItems" based on the column creation_date. As you didn't post the structure of your tables, I had to "invent" a column name. You can use any column that allows you to define an order on the rows that suits you.

But remember the concept of the "last row" is only valid if you specifiy an order or the rows. A table as such is not ordered, nor is the result of a query unless you specify an order by

edited Sep 29 '12 at 14:59

answered Sep 29 '12 at 08:21

i tried both of these with a modification as so: join ( select "EsnId", "SalesOrderItemId", row_number() over (order by "createdAt" desc) as rn from "EsnsSalesOrderItems" ) t ON t."EsnId" = "Esns"."id" and rn = 1 I needed "SalesOrderItemId" so i can use it with next join as it was throwing a missing "EsnsSalesOrderItems" FROM clause error. The result was empty though even with that. Same with the query in the answer below. Can i be doing something wrong? – user1175817 Sep 29 '12 at 12:37
There is no `EsnsSalesOrderItems` "table" any longer. It's now called `t`. You can either change the alias `t` to `EsnsSalesOrderItems` or simply use `t` instead throughout the query – Sep 29 '12 at 12:47
my query is identical to above except i replaced the two join statements with the following:join ( select "EsnId", "SalesOrderItemId", row_number() over (order by "createdAt" desc) as rn from "EsnsSalesOrderItems" ) t ON t."EsnId" = "Esns"."id" and rn = 1 JOIN "SalesOrderItems" on("SalesOrderItems"."id" = t."SalesOrderItemId") – user1175817 Sep 29 '12 at 12:51
`LIMIT 1` would limit to one row, period. But we need one row per `"EsnId"`. Accordingly, the first version with `row_number()` would need a `PARTITION BY "EsnId"` to work. – Erwin Brandstetter Sep 29 '12 at 14:19
@ErwinBrandstetter: good point. Thanks for pointing that out. – Sep 29 '12 at 14:59

Stefan Steiger · Answer 3 · 2019-07-17T07:48:38.267

Necromancing because the answers are outdated.
Take advantage of the LATERAL keyword introduced in PG 9.3

left | right | inner JOIN LATERAL

I'll explain with an example:
Assuming you have a table "Contacts".
Now contacts have organisational units.
They can have one OU at a point in time, but N OUs at N points in time.

Now, if you have to query contacts and OU in a time period (not a reporting date, but a date range), you could N-fold increase the record count if you just did a left join.
So, to display the OU, you need to just join the first OU for each contact (where what shall be first is an arbitrary criterion - when taking the last value, for example, that is just another way of saying the first value when sorted by descending date order).

In SQL-server, you would use cross-apply (or rather OUTER APPLY since we need a left join), which will invoke a table-valued function on each row it has to join.

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989

-- CROSS APPLY -- = INNER JOIN 
OUTER APPLY    -- = LEFT JOIN 
(
    SELECT TOP 1 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
) AS FirstOE

In PostgreSQL, starting from version 9.3, you can do that, too - just use the LATERAL keyword to achieve the same:

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989


LEFT JOIN LATERAL 
(
    SELECT 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
   LIMIT 1 
) AS FirstOE

I needed to add `... ) AS FirstOE ON TRUE` on the end. Apparently `LEFT JOIN` always requires a condition. — ruohola, Jan 26 '22 at 20:13
@ruohola: or ON 1=1, that would be more sql-standards-compliant — Stefan Steiger, Mar 03 '23 at 11:31

Dondi Michael Stroma · Answer 4 · 2012-09-29T07:34:24.650

3

Try using a subquery in your ON clause. An abstract example:

SELECT 
    *
FROM table1
JOIN table2 ON table2.id = (
    SELECT id FROM table2 WHERE table2.table1_id = table1.id LIMIT 1
)
WHERE 
    ...

edited Sep 29 '12 at 07:34

answered Sep 29 '12 at 07:25

Dondi Michael Stroma

4,668
18
21

2

`LIMIT 1` would limit to one row on total. But we need one row per `"EsnId"`. – Erwin Brandstetter Sep 29 '12 at 14:20
@Erwin Brandstetter: Correct, he misses the **LATERAL** keyword. If he had written INNER JOIN **LATERAL** instead of just JOIN, it would work (on PG >= 9.3). But he should also have added an order by for the LIMIT 1. – Stefan Steiger Feb 05 '16 at 10:22

Limit join to one row

4 Answers4

Linked