Grouping aggregate functions

[Pelle Pels]

Hi.

I have this SQL:

SELECT  distinct ARTIK.BEN as Namn, art_nr as nr, ISNULL(A_PRIS,0) as pris, lev_datum
FROM ordra, ARTIK
where ARTIK.NR = ORDRA.art_nr and lev_datum >= '2009-06-01' and lev_datum <= '2010-11-30' and kund_nr = '150'
and ARTIK.NR = '011'
order by art_nr,lev_datum
 
 

Output:

XL POTATIS     011       5.65    2010-06-11
XL POTATIS     011       5.65    2010-06-14 
XL POTATIS     011        5.65    2010-06-15 
XL POTATIS     011       5.65    2010-06-17 
XL POTATIS     011        6.15    2010-09-10 
XL POTATIS     011     6.15    2010-09-16 
XL POTATIS      011       6.15    2010-09-21 
XL POTATIS     011        6.15    2010-09-22 
XL POTATIS     011      5.65    2010-09-27 
XL POTATIS     011        5.65    2010-09-30
 

But I want to track the price-changes so the output to be :

XL POTATIS 011     5.65    2010-06-11 (first date 5.65)
XL POTATIS 011     5.65    2010-06-17 (last date 5.65)
XL POTATIS 011     6.15    2010-09-10 (first date 6.15)
XL POTATIS 011     6.15    2010-09-22 (last date 6.15)
XL POTATIS 011    5.65    2010-09-27 (first date 5.65 again)
....
 

How to manage that?
(The problem is that the price 5.65 has been used before, otherwise is possible with group by)
Pelle

[NeoPa]

Apart from telling you that you probably want to link your tables using a JOIN, there is little I can say.

Your explanation of what you are doing or want to do tells me very little I'm afraid.