Select entire row with group by on last record-CodePudding

I am trying to select only last from group by but failing each time.

With this query:

SELECT
    s.NABAVNACENA, s.ROBAID, MAX(d.DATUM) AS DATUM
FROM 
    DOKUMENT d
LEFT OUTER JOIN 
    STAVKA s ON s.VRDOK = d.VRDOK AND s.BRDOK = d.BRDOK
WHERE
    d.VRDOK IN (0, 11, 18, 16, 22, 1, 2, 26, 29, 30)
    AND d.DATUM >= '01.01.2021'
    AND d.DATUM <= '31.12.2021'
    AND s.ROBAID IN (39, 34)
GROUP BY 
    s.ROBAID, s.NABAVNACENA

I am getting these results

NABAVNACENA   ROBAID   DATUM
---------------------------------
149           39       01.01.2021
130           39       03.01.2021
137           39       08.01.2021
847           34       02.01.2021
820           34       03.01.2021

However, the desired result is only two rows grouped by ROBAID with max date.

In this case:

137     39    08.01.2021
820     34    03.01.2021

When I exclude s.NABAVNACENA from query it does return only these two rows, but when I include it it shows everything.

I tried a lot of things from Stack Overflow that I lost count and now I do not know what to do.

EDIT:

Here is table structure

/* Table: STAVKA, Owner: SYSDBA */

CREATE TABLE "STAVKA" 
(
    "STAVKAID"   INTEGER NOT NULL,
    "VRDOK"      SMALLINT NOT NULL,
    "BRDOK"      INTEGER NOT NULL,
    "MAGACINID"  SMALLINT NOT NULL,
    "ROBAID"       INTEGER NOT NULL,
    "VRSTA"        SMALLINT,
    "NAZIV"        VARCHAR(200),
    "NABCENSAPOR"  NUMERIC(15,4),
    "FAKTURNACENA" NUMERIC(15,4),
    "NABCENABT"    DOUBLE PRECISION,
    "TROSKOVI"     NUMERIC(15,4),
    "NABAVNACENA"  NUMERIC(15,4) NOT NULL,
    "PRODCENABP"   NUMERIC(15,4) NOT NULL,
    "KOREKCIJA"    DOUBLE PRECISION,
    "PRODAJNACENA" NUMERIC(15,2) NOT NULL,
    "DEVIZNACENA"  NUMERIC(15,4) NOT NULL,
    "DEVPRODCENA"  NUMERIC(15,4),
    "KOLICINA"     NUMERIC(15,3) NOT NULL,
    "NIVKOL"       NUMERIC(15,3) NOT NULL,
    "TARIFAID"     VARCHAR(3),
    "IMAPOREZ"     SMALLINT,
    "POREZ"        NUMERIC(15,2) NOT NULL,
    "RABAT"        NUMERIC(15,2) NOT NULL,
    "MARZA"        NUMERIC(15,2) NOT NULL,
    "TAKSA"        NUMERIC(15,4),
    "AKCIZA"       NUMERIC(15,2),
    "PROSNAB"      NUMERIC(15,4) NOT NULL,
    "PRECENA"      NUMERIC(15,4) NOT NULL,
    "PRENAB"       NUMERIC(15,4) NOT NULL,
    "PROSPROD"     NUMERIC(15,4) NOT NULL,
    "MTID"         VARCHAR(10),
    "PT"           CHAR(1) NOT NULL,
    "ZVEZDICA"     VARCHAR(6),
    "TREN_STANJE"  NUMERIC(15,3),
    "POREZ_ULAZ"   NUMERIC(15,2) NOT NULL,
    "SDATUM"       DATE,
    "DEVNABCENA"   NUMERIC(15,4),
    "POREZ_IZ"     NUMERIC(15,2) NOT NULL,
    "X4"           NUMERIC(15,3),
    "Y4"           NUMERIC(15,3),
    "Z4"           NUMERIC(15,3),
    "CENAPOAJM"    NUMERIC(15,2),
    "KGID"         INTEGER,
    "SAKCIZA"      NUMERIC(15,4) NOT NULL,

    CONSTRAINT "STAVKAPRIMARYKEY" PRIMARY KEY ("STAVKAID")
);

I would like to export data but there are 500k rows and do not know how it would be here.

So basically STAVKA is DOCUMENT_ITEM inside some DOCUMENT That item holds ProductID (ROBAID), Price (NABAVNACENA) and other data that are for that product in that document.

So now I want to go through all STAVKA (DOCUMENT_ITEM) and select last Price (NABAVNACENA) for each ROBAID.

For single one I managed to do it like this:

SELECT
    t1.STAVKAID, s.ROBAID, s.NABAVNACENA
FROM
    (SELECT FIRST 1 s.STAVKAID, d.DATUM
     FROM DOKUMENT d
     LEFT OUTER JOIN STAVKA s ON s.VRDOK = d.VRDOK AND s.BRDOK = d.BRDOK
     WHERE d.VRDOK IN (0, 11, 18, 16, 22, 1, 2, 26, 29, 30)
       AND d.DATUM >= '01.01.2021'
       AND d.DATUM <= '31.12.2021'
       AND s.ROBAID = 39
    ORDER BY DATUM DESC) AS t1 
LEFT OUTER JOIN 
    STAVKA s ON t1.STAVKAID = s.STAVKAID

It returns multiple rows, but I order them by DATUM DESC and select first one, that way I selected last STAVKA for that ROBAID

Since I have 4k ROBAID I do not want to run this query 4k times from my code but to return whole table to it

CodePudding user response：

One of the really useful things in SQL is that you can nearly always replace a tablename in a from clause with a query:

SELECT
    s.NABAVNACENA, x.ROBAID, x.DATUM
    from DOKUMENT d
    LEFT OUTER JOIN STAVKA s ON s.VRDOK = d.VRDOK AND s.BRDOK = d.BRDOK
    inner join
    (
      select s.ROBAID, max(d.DATUM) DATUM
      from DOKUMENT d
      LEFT OUTER JOIN STAVKA s ON s.VRDOK = d.VRDOK AND s.BRDOK = d.BRDOK
      WHERE
      d.VRDOK IN (0, 11, 18, 16, 22, 1, 2, 26, 29, 30)
      AND d.DATUM >= '01.01.2021'
      AND d.DATUM <= '31.12.2021'
      AND s.ROBAID IN (39, 34)
      GROUP BY s.ROBAID
    ) x on s.Robaid = x.robaid and d.datum = x.datum

The extra inner join is the query which brings in the two rows wanted but doesn't have NABAVNACENA, and uses it to restrict the original query to those two rows.

CodePudding user response：

Can you just join the table onto itself? I would better use synthetic ID (always incrementing primary key, like AutoInc field in some databases, Generator Trigger in FB2), but okay, let's try with dates. Just... Take care that you would not have TWO or more rows with exactly the same date in one group.

Helping would be CTE introduced in FB 2.1: https://firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-cte

WITH DOKSTAV AS (
  SELECT 
    s.NABAVNACENA, s.ROBAID, d.DATUM
  FROM 
    DOKUMENT d, STAVKA s 
  WHERE s.VRDOK = d.VRDOK 
    AND s.BRDOK = d.BRDOK
)

SELECT
  s1.NABAVNACENA, s1.ROBAID, s1.DATUM
FROM 
  DOKSTAV s1 
LEFT OUTER JOIN 
  DOKSTAV s2
       ON (s1.NABAVNACENA = s2.NABAVNACENA)
      AND (s1.ROBAID = s2.ROBAID)
      AND (s1.DATUM < s2.DATUM)
WHERE s2.DATUM IS NULL