Re: Problem with max(ROW)

From: Andreas Cavaleri <cav_at_hebenstreit.de>
Date: Wed, 2 Oct 2002 07:50:57 +0200
Message-ID: <ane1l6$d7rc6$1_at_ID-135634.news.dfncis.de>

Thank you for your help, your script

##########################

select * from pprk_aktuell
where (artikel_nr, preis_kz, g_datum)
in (select artikel_nr, preis_kz, max(g_datum) from pprk_aktuell
group by artikel_nr, preis_kz)

##########################

does just what I want!
Btw, thanks too to Angelo, who mails me the following Script,

############################

select a.*
from pprk_aktuell a,
(select artikel_nr, preis_kz, max(g_datum) max_g_datum

   from pprk_aktuell
  group by artikel_nr, preis_kz) b

where a.artikel_nr = b.artikel_nr
  and a.preis_kz   = b.preis_kz
  and a.g_datum    = b.max_g_datum

/

###########################

which has the same result.
Thanks to all !

"Andreas Cavaleri" <cav_at_hebenstreit.de> schrieb im Newsbeitrag news:7617730a.0210010348.2918ced6_at_posting.google.com...
> Hi, I need help with a SQL-statement:
> My Table-structure is as follow:
>
> SQLWKS> describe pprk_aktuell
> Spaltenname Null? Typ
> ------------------------------ -------- ----
> ARTIKEL_NR NOT NULL VARCHAR2(35)
> PREIS_KZ NOT NULL VARCHAR2(1)
> G_DATUM NOT NULL VARCHAR2(8)
> PREIS_VK NUMBER(10,2)
> PREIS_TK NUMBER(10,2)
> PPRKPHYSSEQ NOT NULL VARCHAR2(15)
>
> For every ARTIKEL_NR exists up to 6 PREIS_KZ's, it can be A-F.
> For every Preis_KZ there can be multiple rows, but one who has the
> highest G_DATUM.
> I need a Statement which gives only the row with the highest G_DATUM
> for every Group with same ARTIKEL_NR and PREIS_KZ.
> Can anybody help me?
> Thank you for reading!
Received on Wed Oct 02 2002 - 07:50:57 CEST