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(Produ ctName,

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

-----------------------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","Effecti veDate"

"AAA","Prod uct One (MXT234)",1000, 2003-08-30 00:00:00

"BBB","Prod uct One (MXT234)",1000, 2003-07-29 00:00:00

"BBB","Prod uct One (MXT234)",28.15 ,2003-07-30 00:00:00

"BBB","Prod uct One (MXT234)",35.22 ,2003-08-04 00:00:00

"BBB","Prod uct One (MXT234)",35.22 ,2003-08-04 00:00:00

"BBB","Prod uct One (MXT234)",1000, 2003-08-16 00:00:00

"BBB","Prod uct One (MXT234)",33.8, 2003-08-27 00:00:00

"CCC","Prod uct One (MXT234)",33.25 ,2003-08-31 00:00:00

"DDD","Prod uct One (MXT234)",46.25 ,2003-01-02 00:00:00

"EEE","Prod uct One (MXT234)",1000, 2003-08-30 00:00:00

"FFF","Prod uct One (MXT234)",1000, 2003-08-30 00:00:00

"GGG","Prod uct One (MXT234)",1000, 2003-09-01 00:00:00

"HHH","Prod uct One (MXT234)",33.8, 2003-08-04 00:00:00

"III","Prod uct One (MXT234)",1000, 2003-01-13 00:00:00

"JJJ","Prod uct One (MXT234)",34.35 ,2003-07-30 00:00:00

"JJJ","Prod uct One (MXT234)",34.35 ,2003-09-01 00:00:00

"KKK","Prod uct One (MXT234)",1000, 2003-08-30 00:00:00

"ZZZ","Prod uct One (MXT205)",54,20 03-01-13 00:00:00

"AAA","Prod uct One (MXT205)",1000, 2003-08-30 00:00:00

"BBB","Prod uct One (MXT205)",33.95 ,2003-07-27 00:00:00

"BBB","Prod uct One (MXT205)",33.95 ,2003-07-29 00:00:00

"BBB","Prod uct One (MXT205)",35.22 ,2003-08-04 00:00:00

"BBB","Prod uct One (MXT205)",33.8, 2003-08-16 00:00:00

"BBB","Prod uct One (MXT205)",33.5, 2003-08-27 00:00:00

"CCC","Prod uct One (MXT205)",1000, 2003-08-30 00:00:00

"DDD","Prod uct One (MXT205)",46.25 ,2003-01-02 00:00:00

"EEE","Prod uct One (MXT205)",1000, 2003-08-30 00:00:00

"FFF","Prod uct One (MXT205)",1000, 2003-08-30 00:00:00

"GGG","Prod uct One (MXT205)",1000, 2003-09-01 00:00:00

"HHH","Prod uct One (MXT205)",33.95 ,2003-08-04 00:00:00

"III","Prod uct One (MXT205)",1000, 2003-01-13 00:00:00

"JJJ","Prod uct One (MXT205)",34.35 ,2003-07-30 00:00:00

"JJJ","Prod uct One (MXT205)",34.35 ,2003-09-01 00:00:00

"KKK","Prod uct One (MXT205)",1000, 2003-08-30 00:00:00

"ZZZ","Prod uct One (MXT548)",54,20 03-01-13 00:00:00

"AAA","Prod uct One (MXT548)",25.04 ,2003-08-31 00:00:00

"BBB","Prod uct One (MXT548)",33.95 ,2003-07-22 00:00:00

"BBB","Prod uct One (MXT548)",33.95 ,2003-07-27 00:00:00

"BBB","Prod uct One (MXT548)",35.22 ,2003-08-04 00:00:00

"BBB","Prod uct One (MXT548)",33.8, 2003-08-16 00:00:00

"BBB","Prod uct One (MXT548)",33.8, 2003-08-27 00:00:00

"CCC","Prod uct One (MXT548)",1000, 2003-08-30 00:00:00

"DDD","Prod uct One (MXT548)",46.25 ,2003-01-02 00:00:00

"EEE","Prod uct One (MXT548)",1000, 2003-08-30 00:00:00

"FFF","Prod uct One (MXT548)",1000, 2003-08-30 00:00:00

"GGG","Prod uct One (MXT548)",1000, 2003-09-01 00:00:00

"HHH","Prod uct One (MXT548)",33.8, 2003-08-04 00:00:00

"III","Prod uct One (MXT548)",1000, 2003-01-13 00:00:00

"JJJ","Prod uct One (MXT548)",34.35 ,2003-07-30 00:00:00

"JJJ","Prod uct One (MXT548)",34.35 ,2003-09-01 00:00:00

"KKK","Prod uct One (MXT548)",1000, 2003-08-30 00:00:00

"ZZZ","Prod uct Two (DGT6789)",54,2 003-01-13 00:00:00

"AAA","Prod uct Two (DGT6789)",1000 ,2003-08-30 00:00:00

"BBB","Prod uct Two (DGT6789)",1000 ,2003-07-22 00:00:00

"BBB","Prod uct Two (DGT6789)",44.7 5,2003-07-27 00:00:00

"BBB","Prod uct Two (DGT6789)",44.7 5,2003-07-29 00:00:00

"BBB","Prod uct Two (DGT6789)",44.3 5,2003-07-30 00:00:00

"BBB","Prod uct Two (DGT6789)",44.7 5,2003-08-04 00:00:00

"BBB","Prod uct Two (DGT6789)",44.7 5,2003-08-04 00:00:00

"BBB","Prod uct Two (DGT6789)",1000 ,2003-08-23 00:00:00

"BBB","Prod uct Two (DGT6789)",44.7 5,2003-08-27 00:00:00

"CCC","Prod uct Two (DGT6789)",1000 ,2003-08-30 00:00:00

"DDD","Prod uct Two (DGT6789)",46.2 5,2003-05-08 00:00:00

"EEE","Prod uct Two (DGT6789)",1000 ,2003-08-30 00:00:00

"FFF","Prod uct Two (DGT6789)",1000 ,2003-08-30 00:00:00

"GGG","Prod uct Two (DGT6789)",1000 ,2003-09-01 00:00:00

"HHH","Prod uct Two (DGT6789)",46.1 ,2003-08-04 00:00:00

"III","Prod uct Two (DGT6789)",1000 ,2003-01-13 00:00:00

"JJJ","Prod uct Two (DGT6789)",47.4 5,2003-08-04 00:00:00

"JJJ","Prod uct Two (DGT6789)",47.4 5,2003-09-01 00:00:00

"KKK","Prod uct Two (DGT6789)",44.7 5,2003-07-14 00:00:00

"ZZZ","Prod uct Two (DGT6704)",54,2 003-01-13 00:00:00

"AAA","Prod uct Two (DGT6704)",1000 ,2003-08-30 00:00:00

"BBB","Prod uct Two (DGT6704)",45.5 ,2003-07-22 00:00:00

"BBB","Prod uct Two (DGT6704)",46.1 ,2003-08-04 00:00:00

"BBB","Prod uct Two (DGT6704)",46.1 ,2003-08-16 00:00:00

"BBB","Prod uct Two (DGT6704)",44.7 5,2003-08-27 00:00:00

"CCC","Prod uct Two (DGT6704)",1000 ,2003-08-30 00:00:00

"DDD","Prod uct Two (DGT6704)",46.2 5,2003-05-08 00:00:00

"EEE","Prod uct Two (DGT6704)",1000 ,2003-08-30 00:00:00

"FFF","Prod uct Two (DGT6704)",1000 ,2003-08-30 00:00:00

"GGG","Prod uct Two (DGT6704)",1000 ,2003-09-01 00:00:00

"HHH","Prod uct Two (DGT6704)",46.1 ,2003-08-04 00:00:00

"III","Prod uct Two (DGT6704)",1000 ,2003-01-13 00:00:00

"JJJ","Prod uct Two (DGT6704)",47.4 5,2003-08-04 00:00:00

"JJJ","Prod uct Two (DGT6704)",47.4 5,2003-09-01 00:00:00

"KKK","Prod uct Two (DGT6704)",44.7 5,2003-07-14 00:00:00

"ZZZ","Prod uct Three (QAT6785)",52,2 003-01-13 00:00:00

"AAA","Prod uct Three (QAT6785)",1000 ,2003-08-30 00:00:00

"BBB","Prod uct Three (QAT6785)",45.5 ,2003-07-22 00:00:00

"BBB","Prod uct Three (QAT6785)",532. 25,2003-07-29 00:00:00

"BBB","Prod uct Three (QAT6785)",1000 ,2003-07-30 00:00:00

"BBB","Prod uct Three (QAT6785)",1000 ,2003-08-04 00:00:00

"BBB","Prod uct Three (QAT6785)",46.1 ,2003-08-16 00:00:00

"BBB","Prod uct Three (QAT6785)",44.7 5,2003-08-27 00:00:00

"CCC","Prod uct Three (QAT6785)",42.6 5,2003-08-31 00:00:00

"DDD","Prod uct Three (QAT6785)",46.2 5,2003-05-08 00:00:00

"EEE","Prod uct Three (QAT6785)",1000 ,2003-08-30 00:00:00

"FFF","Prod uct Three (QAT6785)",1000 ,2003-08-30 00:00:00

"GGG","Prod uct Three (QAT6785)",1000 ,2003-09-01 00:00:00

"HHH","Prod uct Three (QAT6785)",46.1 ,2003-08-04 00:00:00

"III","Prod uct Three (QAT6785)",1000 ,2003-01-13 00:00:00

"JJJ","Prod uct Three (QAT6785)",40.4 5,2003-08-04 00:00:00

"JJJ","Prod uct Three (QAT6785)",40.4 5,2003-09-01 00:00:00

"KKK","Prod uct Three (QAT6785)",44.7 5,2003-07-14 00:00:00

"ZZZ","Prod uct Three (QAT556)",50.23 ,2003-01-13 00:00:00

"AAA","Prod uct Three (QAT556)",1000, 2003-08-30 00:00:00

"BBB","Prod uct Three (QAT556)",44.75 ,2003-07-22 00:00:00

"BBB","Prod uct Three (QAT556)",532.2 5,2003-07-29 00:00:00

"BBB","Prod uct Three (QAT556)",1000, 2003-07-30 00:00:00

"BBB","Prod uct Three (QAT556)",1000, 2003-08-03 00:00:00

"BBB","Prod uct Three (QAT556)",1000, 2003-08-04 00:00:00

"BBB","Prod uct Three (QAT556)",44.75 ,2003-08-16 00:00:00

"BBB","Prod uct Three (QAT556)",44.75 ,2003-08-27 00:00:00

"CCC","Prod uct Three (QAT556)",1000, 2003-08-30 00:00:00

"DDD","Prod uct Three (QAT556)",46.25 ,2003-05-08 00:00:00

"EEE","Prod uct Three (QAT556)",1000, 2003-08-30 00:00:00

"FFF","Prod uct Three (QAT556)",1000, 2003-08-30 00:00:00

"GGG","Prod uct Three (QAT556)",1000, 2003-09-01 00:00:00

"HHH","Prod uct Three (QAT556)",46.1, 2003-08-04 00:00:00

"III","Prod uct Three (QAT556)",1000, 2003-01-13 00:00:00

"JJJ","Prod uct Three (QAT556)",40.45 ,2003-08-04 00:00:00

"JJJ","Prod uct Three (QAT556)",40.45 ,2003-09-01 00:00:00

"KKK","Prod uct Three (QAT556)",44.75 ,2003-07-14 00:00:00

"JJJ","Prod uct Three (QAT556)",40.15 ,2003-08-01 00:00:00