multiple distinct and time...

21 Oct 2005 8:59 AM

Petr SIMUNEK

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...                 Pettt

21 Oct 2005 12:17 PM

Tarik

Hi ,
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

Show quote

"Petr SIMUNEK" wrote:

> 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...                 Pettt
>
>
>
>
>