[EMAIL PROTECTED] wrote:
>
> Hello !
> SQL-Syntax help needed.
>
> I have a maxdb table with the complete purchase orders from the last 10
> years !
> partno, supplierno, date, amount, price .... (250000 records)
>
> For all partnos there can be orders from different supplieres with
> different prices
>
> I have the transfer the "latest price informations" for each
> partno/supplier to another system
>
> I do a "select * order by partno,supplierno, date desc"
>
> But i want to extract only the last 2 orders for each partno,supplier
> kombination
>
> Can this be done with one sql-command ???
I do not think so
> If not, how to code ??
>
2 possibilities I see:
I )
declare first_cursor cursor for select * from purchase_order
order by partno,supplierno, "date" desc
declare second_cursor cursor for select *, rowno my_rowno from first_cursor
declare third cursor for select partno,supplierno,min(my_rowno) minrowno
from second_cursor group by partno,supplierno
select second_cursor.*
from second_cursor, third
where second_cursor.partno = third.partno
and second_cursor.supplierno = third.supplierno
and second_cursor.my_rowno >= third.minrowno
and second_cursor.my_rowno <= third.minrowno + 1
II )
declare first_cursor cursor for select partno,supplierno, max("date")
from purchase_order
group by partno,supplierno
select * from purchase_order
where (partno,supplierno, "date") in (select * from first_cursor)
union all
select * from purchase_order where
(partno,supplierno, "date") in
(select partno,supplierno, max("date") from purchase_order
where (partno,supplierno, "date") not in
(select * from first_cursor)
group by partno,supplierno)
Good luck
Elke
SAP Labs Berlin
> Any help welcomed
>
> Best regards
>
> Albert Beermann
>
> '''''
> '''''''''
> (0 0)
> +---------oOO-------(_)-------------------+
> | Tel: 0541/5841-868 |
> | Fax: 0541/5841-869 |
> | Mail: mailto:[EMAIL PROTECTED] |
> | Internet: http://www.piepenbrock.de |
> +-------------------------------oOO-------+
> |__|__|
> || ||
> ooO Ooo
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
