-2

I edited my question to make it more understanding. I have 3 tables

1. st_kalk

id_artikli|cena_neto|iddg
1066      |25000    |34323
808       |231933   |25234
718       |22999    |34244
718       |22965    |23212

2._artikli

id_artikli
1066      
808       
718          
718          

3.dok

iddg   |datum
34323  |4/22/2022  
25234  |2/16/2021   
23212  |1/29/2022
34244  |2/2/2022

In st_kalk I have column id_artikli as well in the _artikli table, also st_kalk is related to dok by iddg as you can see in my query. I have to join these 3 tables in order to get the correct price ( because some of my products have multiple prices (cena_neto)) based on the latest date (datum). The query below works but only if I set id_artikla to specific one, but wont return every article with latest price and date, and that is what I am supposed to do. I am sorry if this is confusing, it's my first time writing here also I am still learning sql. Thank you

SELECT top 1
      k.id_artikla,
      k.maxdatum,
      cena_neto
      FROM ( SELECT id_artikla,
                    MAX(datum) AS maxdatum,
                    cena_neto 
             FROM st_kalk INNER JOIN dok ON st_kalk.iddg=dok.iddg
             GROUP BY id_artikla,cena_neto) k 
      INNER JOIN _artikli ON k.id_artikla=k.id_artikla
      WHERE k.id_artikla=718
      ORDER BY k.maxdatum DESC

Thank you in advance for your help

Dj1999
  • 1
  • 1
  • 2
    "This doesn't work" doesn't help us help you. Neither does tagging completely different RDBMS; tag the one you are *really* using. – Thom A May 19 '22 at 14:34
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A May 19 '22 at 14:34
  • Your subquery is spitting out a single record for each `id_artikla` but that distinct `id_artikla` is lost when you join tables `st_kalk` and `_artikli` suggesting you have a 1:many relationship in those joins. Essentially the issue is that you have more than one record in `st_kalk` for each `iddg` or more than one record in `_artikli` for each `id_artikla`. When that situation occurs which of the multiple records from those tables are wanting to keep? – JNevill May 19 '22 at 14:37
  • Just use the inner query to grab max date and id. Use those to join and then. You won't reference that table again except for the join. – shawnt00 May 19 '22 at 14:40
  • @JNevill I have multiple records in st_kalk for one id_artikla and need to choose and show only one. I have 3 tables ( _artikli, st_kalk and dok) In st_kalk I have Id_artikla as well in _artikli, also I have iddg in both st_kalk and dok) For example, In st_kalk I have 2 artikla with different prices and need to show the latest price based on the latest date(datum). – Dj1999 May 19 '22 at 16:04
  • Which dbms are you using? (Different products have diffferent features.) – jarlh May 19 '22 at 17:07
  • A [mcve] is a great start when asking SQL questions. – jarlh May 19 '22 at 17:08
  • @Larnu You were right, I wasn't very clear as this is very confusing for me too, I edited my question and used your suggestion for Top 1 row and it does work if I use just one Id, which is not bad but I need to show every row from table with latest price and date – Dj1999 May 19 '22 at 20:00

1 Answers1

0
select distinct t.* from table t
inner join (select id, max(date) date from table group by id) t1
on t.id = t1.id and t.date = t1.date;

I used distinct on select to avoid pulling duplicate records if there exists duplicate latest dates for an Id.

velocity
  • 53
  • 6