CODEWITHSUNDEEP
sqlsql-servervisual-studio
Daniela
Daniela

Reputation: 139

How to select oldest date row from each product using SQL

I would like to get the oldest only one from every type product and sum of the prices listed in listofproduct table. Another thing is to search only between prodacts that has at least one peace on lager.

With the SQL I managed to get all the products has at least one on the stock. But the rest I am stack...

So the sum cold be done later, that was my plan, but if you have better idea feel free to write

Here is my data:

+-------------+----------------+---------------+----------+
| IDProizvoda | NazivProizvoda | DatumKupovine | NaLageru |
+-------------+----------------+---------------+----------+
|          77 | Cokolada       | 25-Feb-20     |        2 |
|          44 | fgyhufrthr     | 06-Aug-20     |        5 |
|          55 | Auto           | 06-Aug-23     |        0 |
|          55 | Auto           | 11-Aug-20     |      200 |
|          77 | Cokolada       | 06-Aug-27     |        0 |
|          77 | Cokolada       | 25-Feb-20     |       10 |
|          77 | Cokolada       | 25-Jan-20     |      555 |
|          77 | Cokolada       | 25-Mar-20     |       40 |
+-------------+----------------+---------------+----------+

    Access.ExeQuery("SELECT * FROM Products " &
                    "WHERE IDProizvoda IN (SELECT value FROM STRING_SPLIT(@listofproduct, ',')) " &
                    "AND NaLageru > 0 ")

I tried to add GROUP BY and HAVING but it does not worked because i choose the whole table. But I need Product ID and Stock field for edit it later, to subtract one from the stock for those products.

I would like to get the result:

+-------------+----------------+---------------+----------+
| IDProizvoda | NazivProizvoda | DatumKupovine | NaLageru |
+-------------+----------------+---------------+----------+
|          44 | fgyhufrthr     | 06-Aug-20     |        5 |
|          55 | Auto           | 11-Aug-20     |      200 |
|          77 | Cokolada       | 25-Jan-20     |      555 |
+-------------+----------------+---------------+----------+

Thank you for all the help.

Upvotes: 1

Views: 275

Answers (3)

Ben Thul
Ben Thul

Reputation: 32737

This should do it:

with cte as (
    SELECT *, row_number() over (
       partition by NazivProizvoda
       order by DatumKupovine
    ) as rn
    FROM Products
    WHERE IDProizvoda IN (
        SELECT value
        FROM STRING_SPLIT(@listofproduct, ',')
    )
        AND NaLageru > 0
)
select *
from cte
where rn = 1;

By way of explanation, I'm using a common table expression to select the superset of the data you want by criteria and adding a column that enumerates each row within a group (a group being defined here as having NazivProizvoda be the same) in order of the DatumKupovine). With that done, anything that admits the value of 1 for that enumeration will be the oldest in the group. If you data is such that more than one row can be the oldest, use rank() instead of row_number().

Upvotes: 1

iceblade
iceblade

Reputation: 641

You can do it with a Cross Apply, this would be your SQL query:

Select  P.IDProizvoda,
        P.NazivProizvoda,
        N.DatumKupovine,
        N.NaLageru,
        N.IDKupovine,
        N.CenaPoKomadu
        From 
products P
Cross Apply
(
    Select top 1    DatumKupovine,
                    NaLageru,
                    IDKupovine,
                    CenaPoKomadu
    From products P2
    where P2.IDProizvoda = P.IDProizvoda
    and P2.NaLageru > 0 
    order by DatumKupovine
)  N
group by P.IDProizvoda, P.NazivProizvoda, N.DatumKupovine, N.NaLageru, N.IDKupovine, N.CenaPoKomadu

And this your ExeQuery:

Access.ExeQuery("Select P.IDProizvoda, P.NazivProizvoda, N.DatumKupovine, N.NaLageru, N.IDKupovine, N.CenaPoKomadu From products P " &
        " Cross Apply( Select top 1 DatumKupovine, NaLageru, IDKupovine, CenaPoKomadu From products P2 where P2.IDProizvoda = P.IDProizvoda and P2.NaLageru > 0 order by DatumKupovine)  N " &
        " where P.IDProizvoda in (Select value From STRING_SPLIT(@listofproduct, ',')) " &
        " group by P.IDProizvoda,   P.NazivProizvoda, N.DatumKupovine, N.NaLageru, N.IDKupovine, N.CenaPoKomadu " )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think this is just aggregation with a filter:

SELECT IDProizvoda, NazivProizvoda, MAX(DatumKupovine),
       SUM(NaLegaru)
FROM Products p
WHERE NaLegaru > 0
GROUP BY IDProizvoda, NazivProizvoda;

Upvotes: 1

Related Questions