 Need help with query
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
-----------------------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
 |