multiple distinct and time...

21 Oct 2005 6:30 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 6:56 AM

John Bell

Hi

The difference between the first and second query is that you are
resistricting to be a single product. One possible solution would be:

CREATE TABLE #prices ( Id_Prices int, Shop varchar(20), Product varchar(20),
Price int , tStamp datetime )

INSERT INTO #prices ( Id_Prices, Shop, Product, Price, tStamp )
SELECT 1, 'eMall', 'Pen', 10, '20050520'
UNION ALL SELECT 2, 'eMall', 'Pen', 11, '20050521'
UNION ALL SELECT 3, 'eMall', 'Pen', 9, '20050522'
UNION ALL SELECT 4, 'webShop', 'Pen', 10, '20050522'
UNION ALL SELECT 5, 'webShop', 'Pen', 12, '20050523'
UNION ALL SELECT 6, 'InetShop', 'Pen', 10, '20050520'
UNION ALL SELECT 7, 'netOultel', 'Pen', 9, '20050519'
UNION ALL SELECT 8, 'netOultel', 'Pencil', 5, '20050519'
UNION ALL SELECT 9, 'netOultel', 'Pencil', 6, '20050520'

-- For Pen only
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
WHERE p.product = 'Pen'

or alternatively:

SELECT p.*
from #prices p
WHERE tstamp = ( SELECT Max(tstamp)
from #prices q WHERE q.shop = p.shop AND q.product = p.product AND
q.product = 'Pen')
AND p.product = 'Pen'

-- For all products
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp

If you are using this join alot you may want to create a view.

John

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

21 Oct 2005 2:27 PM

Petr SIMUNEK

thanx a lot guys...
It works like a charm of course.... thanks again

PS: would you care to share with me how long experience with SQL do you have
?
(so I know how long the road is... :o))

thanx

21 Oct 2005 3:29 PM

John Bell

Hi

It is a never ending road... when you think you know it all something new
always turns up...and then there is the ageing process where you start to
forget what you have learn't anyhow.

John

Show quote

"Petr SIMUNEK" wrote:

> thanx a lot guys...
> It works like a charm of course.... thanks again
>
> PS: would you care to share with me how long experience with SQL do you have
> ?
> (so I know how long the road is... :o))
>
> thanx
>
>
>