database - SQL select only highest date -

database - SQL select only highest date -

for project want generate cost list. want latest prices each supplier each article.

there 2 tables.

table articles artnr | txt | active | supplier ------------------------------------------ 10 | apple | y | 10 20 | orange | y | 10 30 | keyboard | n | 20 40 | orange | y | 20 50 | banana | y | 10 60 | cherry | y | 10 table prices artnr | prcgrp | prcdat | cost -------------------------------------- 10 | 10 | 01-aug-10 | 2.1 10 | 10 | 05-aug-11 | 2.2 10 | 10 | 21-aug-12 | 2.5 20 | 0 | 01-aug-10 | 2.1 20 | 10 | 09-aug-12 | 2.3 10 | 10 | 14-aug-13 | 2.7

this have far:

select articles.[artnr], articles.[txt], articles.[active], articles.[supplier], prices.prcgrp, prices.prcdat, prices.price articles inner bring together prices on articles.artnr = prices.artnr ( (articles.[active]="y") , (articles.[supplier]=10) , (prices.prcgrp=0) , (prices.prcdat=(select max(prcdat) prices fine art art.artnr = prices.artnr) ) ) order articles.artnr ;

it okay take 1 supplier each time, want max price.

the problem is: lots of articles not show query above, cannot figure out wrong.

i can see should in resultset when leave out subselect on max prcdat.

what wrong?

your subquery latest cost not take other conditions account, when you're getting latest price, may cost in cost grouping or not active. when bring together against filtered list has no inactive prices , prices in single cost group, no hits exist in both.

either need duplicate or - improve - move conditions within subquery best cost under conditions. can't test against access, should possible if sql not limited;

class="lang-sql prettyprint-override">select a.artnr, a.txt, a.active, a.supplier, p.prcgrp, p.prcdat, p.price articles inner bring together prices p on a.artnr = p.artnr bring together ( select a.artnr, max(p.prcdat) prcdat articles bring together prices p on a.artnr = p.artnr a.active='y' , a.supplier=10 , p.prcgrp=10 grouping a.artnr) z on a.artnr = z.artnr , p.prcdat = z.prcdat order a.artnr

if sql back upwards in access won't allow bring together subquery, can move conditions within existing subquery, like;

select a.artnr, a.txt, a.active, a.supplier, p.prcgrp, p.prcdat, p.price articles inner bring together prices p on a.artnr = p.artnr p.prcdat = ( select max(p2.prcdat) articles a2 bring together prices p2 on a2.artnr = p2.artnr a.artnr = a2.artnr , a2.active='y' , a2.supplier=10 , p2.prcgrp=10 ) order a.artnr;

note due limitations in identifying unique cost (no primary key in prices), queries may give duplicates if several prices same article have same prcdat. if that's problem, you'll need duplicate conditions outside subquery too.

sql database ms-access select subquery