Limit connection to one line

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 records. I want to limit the request to only pull the last entry in "EsnsSalesOrderItems" , which has the same "EsnId" .

The "last" entry means the following:

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

Answer 1

 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 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 the number in the group?

I retrieve one row for each group in "EsnsSalesOrderItems" using DISTINCT ON . Detailed explanation:

• Select the first row in each GROUP BY?

I also added table aliases and formatting to facilitate query analysis for human eyes. If you could avoid the camel case, you could get rid of all the double quotes , a blurry view.

Answer 2

Sort of:

 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 last < EsnId" from the "EsnsSalesOrderItems" based on the creation_date column. Since you did not place the structure of your tables, I had to" reinvent "the column name. which allows you to determine the order in the rows that suit you.

But remember that the concept of the "last line" is only valid if you specify the order or lines. The table as such is not ordered and is not the result of a query unless order by

Answer 3

Necromancy because answers are out of date.
Use the LATERAL keyword introduced in PG 9.3

left | right | internal JOIN LATERAL

I will explain with an example:
Assuming you have a Contacts table.
Contacts now have organizational units.
They can have one OU at a certain point in time, but N OU at N time points.

Now, if you need to request contacts and OU for a period of time (not a reporting date, but a date range), you can increase the number of entries by N times if you just made a left connection.
So, to display the OU, you just need to attach the first OU for each contact (where what should be the first is an arbitrary criterion - for example, when accepting the last value, this is just another way to say the first value when sorting in descending date).

In a SQL server, you would use cross-application (or rather OUTER APPLY, since we need a left join), which will call a table function on each row to be joined.

 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 with version 9.3, you can do this 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 

Answer 4

Try using a subquery in the ON clause. Abstract example:

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