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
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
"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
>
>|||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|||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
"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 ha
ve
> ?
> (so I know how long the road is... :o))
> thanx
>
>sql