"netpurpose" <netpurpose@hotmail.com> wrote in message
news:31c57b38.0308290040.6a560bd6@posting.google.c om...
> I need to extract data from this table to find the lowest prices of
> each product as of today. The product will be listed/grouped by the
> name only, discarding the product code - I use SUBSTRING(ProductName,
> 1, CHARINDEX('(', ProductName)-2).
>
> I can get this result, but I had to use several views (totally
> inefficient). I think this can be done in one efficient/fast query,
> but I can't think of one.
>
> In the case that one query is not possible, is there other efficient
> way to get the results? There are about 300K records in the table.
>
> Any help is appreciated! Thanks a lot in advance!
>
> Thanks,
> Netpurpose

SELECT P1.ProductName, P1.Price
FROM ProductList AS P1
WHERE EffectiveDate <= CURRENT_TIMESTAMP AND
NOT EXISTS
(SELECT *
FROM ProductList AS P2
WHERE SUBSTRING(P1.ProductName, 1,
CHARINDEX('(', P1.ProductName) - 2) =
SUBSTRING(P2.ProductName, 1,
CHARINDEX('(', P2.ProductName) - 2) AND
P2.EffectiveDate <= CURRENT_TIMESTAMP AND
P2.Price < P1.Price)

Regards,
jag

>
> -----------------------START TABLE INFO-------------------------
>
> Here is the desired result (on 8/29/03):
>
> ProductName Price
> ---------------------------------------- ---------------------
> Product One 33.5000
> Product Three 40.4500
> Product Two 44.7500
>
>
> Here is the table info:
>
> CREATE TABLE [ProductList] (
> [Distributor] [nvarchar] (5) NULL ,
> [ProductName] [nvarchar] (40) NULL ,
> [Price] [money] NULL ,
> [EffectiveDate] [smalldatetime] NULL
> ) ON [PRIMARY]
>
>
> Table Data (comma delimited):
> *Sorry I can't list in insert commands as there are too many.
>
> "Manufacturer","ProductName","Price","EffectiveDat e"
> "AAA","Product One (MXT234)",1000,2003-08-30 00:00:00
> "BBB","Product One (MXT234)",1000,2003-07-29 00:00:00
> "BBB","Product One (MXT234)",28.15,2003-07-30 00:00:00
> "BBB","Product One (MXT234)",35.22,2003-08-04 00:00:00
> "BBB","Product One (MXT234)",35.22,2003-08-04 00:00:00
> "BBB","Product One (MXT234)",1000,2003-08-16 00:00:00
> "BBB","Product One (MXT234)",33.8,2003-08-27 00:00:00
> "CCC","Product One (MXT234)",33.25,2003-08-31 00:00:00
> "DDD","Product One (MXT234)",46.25,2003-01-02 00:00:00
> "EEE","Product One (MXT234)",1000,2003-08-30 00:00:00
> "FFF","Product One (MXT234)",1000,2003-08-30 00:00:00
> "GGG","Product One (MXT234)",1000,2003-09-01 00:00:00
> "HHH","Product One (MXT234)",33.8,2003-08-04 00:00:00
> "III","Product One (MXT234)",1000,2003-01-13 00:00:00
> "JJJ","Product One (MXT234)",34.35,2003-07-30 00:00:00
> "JJJ","Product One (MXT234)",34.35,2003-09-01 00:00:00
> "KKK","Product One (MXT234)",1000,2003-08-30 00:00:00
> "ZZZ","Product One (MXT205)",54,2003-01-13 00:00:00
> "AAA","Product One (MXT205)",1000,2003-08-30 00:00:00
> "BBB","Product One (MXT205)",33.95,2003-07-27 00:00:00
> "BBB","Product One (MXT205)",33.95,2003-07-29 00:00:00
> "BBB","Product One (MXT205)",35.22,2003-08-04 00:00:00
> "BBB","Product One (MXT205)",33.8,2003-08-16 00:00:00
> "BBB","Product One (MXT205)",33.5,2003-08-27 00:00:00
> "CCC","Product One (MXT205)",1000,2003-08-30 00:00:00
> "DDD","Product One (MXT205)",46.25,2003-01-02 00:00:00
> "EEE","Product One (MXT205)",1000,2003-08-30 00:00:00
> "FFF","Product One (MXT205)",1000,2003-08-30 00:00:00
> "GGG","Product One (MXT205)",1000,2003-09-01 00:00:00
> "HHH","Product One (MXT205)",33.95,2003-08-04 00:00:00
> "III","Product One (MXT205)",1000,2003-01-13 00:00:00
> "JJJ","Product One (MXT205)",34.35,2003-07-30 00:00:00
> "JJJ","Product One (MXT205)",34.35,2003-09-01 00:00:00
> "KKK","Product One (MXT205)",1000,2003-08-30 00:00:00
> "ZZZ","Product One (MXT548)",54,2003-01-13 00:00:00
> "AAA","Product One (MXT548)",25.04,2003-08-31 00:00:00
> "BBB","Product One (MXT548)",33.95,2003-07-22 00:00:00
> "BBB","Product One (MXT548)",33.95,2003-07-27 00:00:00
> "BBB","Product One (MXT548)",35.22,2003-08-04 00:00:00
> "BBB","Product One (MXT548)",33.8,2003-08-16 00:00:00
> "BBB","Product One (MXT548)",33.8,2003-08-27 00:00:00
> "CCC","Product One (MXT548)",1000,2003-08-30 00:00:00
> "DDD","Product One (MXT548)",46.25,2003-01-02 00:00:00
> "EEE","Product One (MXT548)",1000,2003-08-30 00:00:00
> "FFF","Product One (MXT548)",1000,2003-08-30 00:00:00
> "GGG","Product One (MXT548)",1000,2003-09-01 00:00:00
> "HHH","Product One (MXT548)",33.8,2003-08-04 00:00:00
> "III","Product One (MXT548)",1000,2003-01-13 00:00:00
> "JJJ","Product One (MXT548)",34.35,2003-07-30 00:00:00
> "JJJ","Product One (MXT548)",34.35,2003-09-01 00:00:00
> "KKK","Product One (MXT548)",1000,2003-08-30 00:00:00
> "ZZZ","Product Two (DGT6789)",54,2003-01-13 00:00:00
> "AAA","Product Two (DGT6789)",1000,2003-08-30 00:00:00
> "BBB","Product Two (DGT6789)",1000,2003-07-22 00:00:00
> "BBB","Product Two (DGT6789)",44.75,2003-07-27 00:00:00
> "BBB","Product Two (DGT6789)",44.75,2003-07-29 00:00:00
> "BBB","Product Two (DGT6789)",44.35,2003-07-30 00:00:00
> "BBB","Product Two (DGT6789)",44.75,2003-08-04 00:00:00
> "BBB","Product Two (DGT6789)",44.75,2003-08-04 00:00:00
> "BBB","Product Two (DGT6789)",1000,2003-08-23 00:00:00
> "BBB","Product Two (DGT6789)",44.75,2003-08-27 00:00:00
> "CCC","Product Two (DGT6789)",1000,2003-08-30 00:00:00
> "DDD","Product Two (DGT6789)",46.25,2003-05-08 00:00:00
> "EEE","Product Two (DGT6789)",1000,2003-08-30 00:00:00
> "FFF","Product Two (DGT6789)",1000,2003-08-30 00:00:00
> "GGG","Product Two (DGT6789)",1000,2003-09-01 00:00:00
> "HHH","Product Two (DGT6789)",46.1,2003-08-04 00:00:00
> "III","Product Two (DGT6789)",1000,2003-01-13 00:00:00
> "JJJ","Product Two (DGT6789)",47.45,2003-08-04 00:00:00
> "JJJ","Product Two (DGT6789)",47.45,2003-09-01 00:00:00
> "KKK","Product Two (DGT6789)",44.75,2003-07-14 00:00:00
> "ZZZ","Product Two (DGT6704)",54,2003-01-13 00:00:00
> "AAA","Product Two (DGT6704)",1000,2003-08-30 00:00:00
> "BBB","Product Two (DGT6704)",45.5,2003-07-22 00:00:00
> "BBB","Product Two (DGT6704)",46.1,2003-08-04 00:00:00
> "BBB","Product Two (DGT6704)",46.1,2003-08-16 00:00:00
> "BBB","Product Two (DGT6704)",44.75,2003-08-27 00:00:00
> "CCC","Product Two (DGT6704)",1000,2003-08-30 00:00:00
> "DDD","Product Two (DGT6704)",46.25,2003-05-08 00:00:00
> "EEE","Product Two (DGT6704)",1000,2003-08-30 00:00:00
> "FFF","Product Two (DGT6704)",1000,2003-08-30 00:00:00
> "GGG","Product Two (DGT6704)",1000,2003-09-01 00:00:00
> "HHH","Product Two (DGT6704)",46.1,2003-08-04 00:00:00
> "III","Product Two (DGT6704)",1000,2003-01-13 00:00:00
> "JJJ","Product Two (DGT6704)",47.45,2003-08-04 00:00:00
> "JJJ","Product Two (DGT6704)",47.45,2003-09-01 00:00:00
> "KKK","Product Two (DGT6704)",44.75,2003-07-14 00:00:00
> "ZZZ","Product Three (QAT6785)",52,2003-01-13 00:00:00
> "AAA","Product Three (QAT6785)",1000,2003-08-30 00:00:00
> "BBB","Product Three (QAT6785)",45.5,2003-07-22 00:00:00
> "BBB","Product Three (QAT6785)",532.25,2003-07-29 00:00:00
> "BBB","Product Three (QAT6785)",1000,2003-07-30 00:00:00
> "BBB","Product Three (QAT6785)",1000,2003-08-04 00:00:00
> "BBB","Product Three (QAT6785)",46.1,2003-08-16 00:00:00
> "BBB","Product Three (QAT6785)",44.75,2003-08-27 00:00:00
> "CCC","Product Three (QAT6785)",42.65,2003-08-31 00:00:00
> "DDD","Product Three (QAT6785)",46.25,2003-05-08 00:00:00
> "EEE","Product Three (QAT6785)",1000,2003-08-30 00:00:00
> "FFF","Product Three (QAT6785)",1000,2003-08-30 00:00:00
> "GGG","Product Three (QAT6785)",1000,2003-09-01 00:00:00
> "HHH","Product Three (QAT6785)",46.1,2003-08-04 00:00:00
> "III","Product Three (QAT6785)",1000,2003-01-13 00:00:00
> "JJJ","Product Three (QAT6785)",40.45,2003-08-04 00:00:00
> "JJJ","Product Three (QAT6785)",40.45,2003-09-01 00:00:00
> "KKK","Product Three (QAT6785)",44.75,2003-07-14 00:00:00
> "ZZZ","Product Three (QAT556)",50.23,2003-01-13 00:00:00
> "AAA","Product Three (QAT556)",1000,2003-08-30 00:00:00
> "BBB","Product Three (QAT556)",44.75,2003-07-22 00:00:00
> "BBB","Product Three (QAT556)",532.25,2003-07-29 00:00:00
> "BBB","Product Three (QAT556)",1000,2003-07-30 00:00:00
> "BBB","Product Three (QAT556)",1000,2003-08-03 00:00:00
> "BBB","Product Three (QAT556)",1000,2003-08-04 00:00:00
> "BBB","Product Three (QAT556)",44.75,2003-08-16 00:00:00
> "BBB","Product Three (QAT556)",44.75,2003-08-27 00:00:00
> "CCC","Product Three (QAT556)",1000,2003-08-30 00:00:00
> "DDD","Product Three (QAT556)",46.25,2003-05-08 00:00:00
> "EEE","Product Three (QAT556)",1000,2003-08-30 00:00:00
> "FFF","Product Three (QAT556)",1000,2003-08-30 00:00:00
> "GGG","Product Three (QAT556)",1000,2003-09-01 00:00:00
> "HHH","Product Three (QAT556)",46.1,2003-08-04 00:00:00
> "III","Product Three (QAT556)",1000,2003-01-13 00:00:00
> "JJJ","Product Three (QAT556)",40.45,2003-08-04 00:00:00
> "JJJ","Product Three (QAT556)",40.45,2003-09-01 00:00:00
> "KKK","Product Three (QAT556)",44.75,2003-07-14 00:00:00
> "JJJ","Product Three (QAT556)",40.15,2003-08-01 00:00:00