multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi ,
Try this :
create table #tmp
(
Id_Prices integer ,
Shop char(9),
Product char(6),
Price integer,
tStamp char (9)
) ;
---
insert into #tmp values (1, 'eMall','Pen',10,'20.5.2005');
insert into #tmp values (2, 'eMall','Pen',11,'21.5.2005');
insert into #tmp values (3, 'eMall','Pen',9,'22.5.2005');
insert into #tmp values (4, 'webShop','Pen',10,'22.5.2005');
insert into #tmp values (5, 'webShop','Pen',12,'23.5.2005');
insert into #tmp values (6, 'InetShop','Pen',10,'20.5.2005');
insert into #tmp values (7, 'netOultel','Pen',9,'19.5.2005');
insert into #tmp values (8, 'netOultel','Pencil',5,'19.5.2005');
insert into #tmp values (9, 'netOultel','Pencil',6,'20.5.2005');
---
select a.* , b.price
from
(
select product , shop , max (tstamp) tstamp
from #tmp
group by product , shop
) a ,
(
select *
from #tmp
) b
where a.shop= b.shop
and a.product = b.product
and a.tstamp = b.tstamp
-- and b.product = 'Pen'
group by a.product , a.shop , a.tstamp , b.price
order by 1
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>
>