CODEWITHSUNDEEP
sqlsql-server
Yanick Lafontaine
Yanick Lafontaine

Reputation: 190

SQL Query - Left Join Group By

I have two tables :

The first one is for receptions details and the second one is for receptions header.

CREATE TABLE ACH_ReceptionsDet
(
    IdReception bigint NOT NULL,
    IdProd bigint,
    QteRecue numeric(16,5),
);

CREATE TABLE ACH_ReceptionsEnt
(
    IdReception bigint PRIMARY KEY NOT NULL,
    DateRec datetime NOT NULL,
);

I would like to get last IdProd, QteRecue, DateRec for each IdProd. In other words I would like to get last reception of each products.

EDIT :

For now i'm able to get DateRecep and IdProd like this (4862 results):

SELECT
  MAX(E.DateRec) AS DateRecp,
  D.IdProd
FROM ogasys.ACH_ReceptionsDet D
  LEFT JOIN ogasys.ACH_ReceptionsEnt E
    ON D.IdReception = E.IdReception
GROUP BY D.IdProd

I cannot add D.QteRecue in select because it is not contained in an aggregate function or in the GROUP BY clause. I don't understand how to do that.

If i run this query (4862 results):

SELECT count(DISTINCT Idprod)
FROM ogasys.ACH_ReceptionsDet

But if i add E.QteRecue (6973 results)

SELECT
      MAX(E.DateRec) AS DateRecp,
      D.IdProd,
      **E.QteRecue**
    FROM ogasys.ACH_ReceptionsDet D
      LEFT JOIN ogasys.ACH_ReceptionsEnt E
        ON D.IdReception = E.IdReception
    GROUP BY D.IdProd, **E.QteRecue**

Upvotes: 1

Views: 74

Answers (3)

Edward N
Edward N

Reputation: 997

You can use sub query like this

    SELECT temp.DateRecp,
        ,temp.IdProd
        ,R.QteRecue
FROM ogasys.ACH_ReceptionsDet R 
INNER JOIN
    (SELECT MAX(E.DateRec) AS DateRecp
            ,D.IdProd  
    FROM ogasys.ACH_ReceptionsDet D
    LEFT JOIN ogasys.ACH_ReceptionsEnt E ON D.IdReception = E.IdReception
    GROUP BY D.IdProd) AS temp ON R.IdProd = temp.IdProd

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You seem to want row_number():

SELECT de.*
FROM (SELECT DateRecp, D.IdProd, D.QteRecue,
             ROW_NUMBER() OVER (PARTITION BY D.IdProd ORDER BY DateRecp DESC) as seqnum
      FROM ogasys.ACH_ReceptionsDet D LEFT JOIN
           ogasys.ACH_ReceptionsEnt E
           ON D.IdReception = E.IdReception
     ) de
WHERE seqnum = 1;

Upvotes: 0

Connor
Connor

Reputation: 215

Adding the column to the group by should fix the issue:

SELECT
  MAX(E.DateRec) AS DateRecp,
  D.IdProd,
  D.QteRecue
FROM ogasys.ACH_ReceptionsDet D
  LEFT JOIN ogasys.ACH_ReceptionsEnt E
    ON D.IdReception = E.IdReception
GROUP BY D.IdProd, D.QteRecue;

Upvotes: 1

Related Questions