Help me with getting recent value of multivalue rows

[XaRz]

Hi all,

I've been searching for the way to do it. I need to show the order details in a sigle row per product. I want to get the results formated as:

orderproductid orderproductunits orderproductprice orderdate

101102 6 40,35 19/10/2007
100100 3 34,68 20/10/1007
101208 4 44,78 11/10/2007

I'm having troubles because there is more than one row for productid getting the result:

orderproductid orderproductunits orderproductprice orderdate

101102 6 40,35 19/10/2007
100100 3 34,68 20/10/1007
101102 1 40,35 11/10/2007
101102 6 40,35 21/10/2007
100100 1 34,68 11/10/1007

And In this example the output that I want to show is :

orderproductid orderproductunits orderproductprice orderdate

100100 3 34,68 20/10/1007
101102 6 40,35 21/10/2007

I'm assuming that the problem is that the fields orderdate,orderproductunits and orderproductprice can get diferent values and the agregate expresion MAX(orderdate) is not agregating the right rows because all rows are diferent. Isn't Right?

How is the best aproach?
And sorry for the newbye question.

 

[PHV]

SELECT A.orderproductid, A.orderproductunits, A.orderproductprice, A.orderdate
FROM yourTable A INNER JOIN (
SELECT orderproductid, MAX(orderdate) LastDate FROM yourTable GROUP BY orderproductid
) B ON A.orderproductid = B.orderproductid AND A.orderdate = B.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[XaRz]

Ok, But I suspect that Firebird doesn't like the join (select..) statment.

see my Firebird SQL:

Select A.ARTI_CODI, A.alvl_unidades,A.alvl_precio, A.Alvc_fecha_alta
from dib_consum_last_articles_2(20013) A INNER JOIN (

Select ARTI_CODI, MAX(ALVC_FECHA_ALTA) LastDate FROM ALVC
JOIN ALVL on ALVL_ALVC_PK=ALVC_PK JOIN ARTI on ALVL_ARTI_PK=ARTI_PK group by arti_codi) B ON A.ARTI_CODI = B.ARTI_CODI AND A.ALVC_FECHA_ALTA = B.LastDate )

On dib_consum_last_articles_2(20013) is a stored procedure like:

SET TERM ^ ;

CREATE PROCEDURE DIB_CONSUM_LAST_ARTICLES_2 (
cliecodi varchar(10))
returns (
arti_desc varchar(100),
data date,
arti_pres varchar(10),
alvl_precio decimal(15,2),
alvl_uni integer,
arti_codi varchar(10),
alvc_codi varchar(10))
as
begin
/* Procedure Text */
for select alvc_codi,arti.ARTI_codi, arti.arti_presentacion,arti.arti_desc, alvl_unidades,alvl_precio,MAX(alvc_Fecha_alta) from alvc
join alvl on alvl_alvc_pk=alvc.alvc_pk
join arti on arti_pk=alvl_arti_pk
join clie on clie_pk=alvc_clie_pk
Where arti.arti_codi<>'' and arti_desc<>'' and clie_codi = :cliecodi
Group by alvc_codi,arti.ARTI_codi, arti.arti_presentacion,arti.arti_desc, alvl_unidades,alvl_precio
into
:alvc_codi,
:arti_codi,
:arti_pres,
:arti_desc,
:alvl_uni,
:alvl_precio,
:data
do begin
suspend;
end
end^

This kind of Join Select doesn't works...any hints?

 

[XaRz]

Damm.

firebird doesn't support subquerys in FROM statment (perhaps doesnt support in join too)

Firebird uses Stored procedures emuling that situation or in where clause.

any hints how to show my output in these terms? (without the subquery, I mean)

Thanks,

 

[r937]

why not ask the guys in forum1278?

this is the ANSI SQL forum

r937.com | rudy.ca

 

[XaRz]

Thanks, r937.

You're completely right. Thanks for the link.