Query Help

[newbie14]

Dear All,
I have a query as below SELECT DISTINCT (productID),totalAmountAfter,transactionDate FROM tbltransaction WHERE transactionDate BETWEEN '2010-01-01' AND '2010-12-31' ORDER BY productID,transactionDate DESC .
My problem now I want is that for each product only pick one line that is the last line since I have ordered by the date. But even using distinct I still get a more than one data for each productID. Any solution for this?

[drjohn]

because the combination containing productid, totalAmountAfter, transactionDate is what the distinct applies to, not just the productID,

[newbie14]

Dear John,
Any solution to my problem? Thank you.

[smantscheff]

Along those lines:

SELECT productID,totalAmountAfter,transactionDate FROM tbltransaction
WHERE transactionDate BETWEEN '2010-01-01' AND '2010-12-31' 
and (productID, transactionDate) in
(SELECT productID, max(transactionDate) from tbltransaction group by productID)
ORDER BY productID,transactionDate DESC

[newbie14]

Dear Smant,
Your query works but it took me 5 minutes plus wow. How to solve the timing issue?

[smantscheff]

Set an index on transactionDate.
Study the output of EXPLAIN <thequery> for optimisation hints.
How often do you have to run the query, anyway? Maybe you are better of by caching the result instead of optimizing the run-time behaviour of the query.

[newbie14]

Dear Smant,
I have added the index is still the same. Here is my explain but key,key_len and ref are all null here.
ID SelectType table type possibleKeys rows extra
"1 PRIMARY tbltransaction ALL NewIndex1 24391 Using where; Using filesort"
"2 DEPENDENT SUBQUERY tbltransaction ALL 24391 Using temporary; Using filesort"

[smantscheff]

Looks like you don't have an index on productID. Isn't that your primary key? Show the output of CREATE TABLE tbltransaction

[newbie14]

Dear Smant,
You are right I am missing that.But the index which I have build do you recommend or shall I remove those?

CREATE TABLE `tbltransaction` (                                                                        
                  `transactionID` INT(10) NOT NULL,                                                                    
                  `transactionDetailsID` INT(10) NOT NULL,                                                             
                  `stockID` INT(10) NOT NULL,                                                                          
                  `productID` INT(5) NOT NULL,                                                                         
                  `outletFromID` INT(2) NOT NULL,                                                                      
                  `outletToID` INT(2) NOT NULL,                                                                        
                  `totalAmountBefore` DOUBLE(10,2) NOT NULL,                                                           
                  `totalAmountAfter` DOUBLE(10,2) NOT NULL,                                                            
                  `totalQuantityBefore` INT(10) NOT NULL,                                                              
                  `totalQuantityAfter` INT(10) NOT NULL,                                                               
                  `averageCostBefore` DOUBLE(10,2) NOT NULL,                                                           
                  `averageCostAfter` DOUBLE(10,2) NOT NULL,                                                            
                  `quantity` INT(10) NOT NULL,                                                                         
                  `costPrice` DOUBLE(10,2) NOT NULL,                                                                   
                  `transactionPrice` DOUBLE(10,2) NOT NULL,                                                            
                  `transactionEmployeeID` INT(2) NOT NULL,                                                             
                  `transactionDate` DATE NOT NULL,                                                                     
                  `transactionTime` TIME NOT NULL,                                                                     
                  `transactionType` ENUM('ti','to','tif','tof','i','r','e','f','p','s','g') NOT NULL,                  
                  PRIMARY KEY (`transactionID`,`transactionDetailsID`,`outletFromID`,`outletToID`,`transactionType`),  
                  KEY `NewIndex1` (`transactionDate`)                                                                  
                ) ENGINE=INNODB DEFAULT CHARSET=latin1

[smantscheff]

Set an index on productID. Test with the EXPLAIN <query> statement if it is used by the query optimiser.
Other indexes which are not used by query optimisation are irrelevant - it does not harm to keep them.

[newbie14]

Dear Smant,
After adding the index on productID it became worse where my whole sql client crash.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tbltransaction	ALL	NewIndex1	\N	\N	\N	24391	Using where; Using filesort
2	DEPENDENT SUBQUERY	tbltransaction	index	\N	NewIndex2	4	\N	13

CREATE TABLE `tbltransaction` (                                                                        
                  `transactionID` INT(10) NOT NULL,                                                                    
                  `transactionDetailsID` INT(10) NOT NULL,                                                             
                  `stockID` INT(10) NOT NULL,                                                                          
                  `productID` INT(5) NOT NULL,                                                                         
                  `outletFromID` INT(2) NOT NULL,                                                                      
                  `outletToID` INT(2) NOT NULL,                                                                        
                  `totalAmountBefore` DOUBLE(10,2) NOT NULL,                                                           
                  `totalAmountAfter` DOUBLE(10,2) NOT NULL,                                                            
                  `totalQuantityBefore` INT(10) NOT NULL,                                                              
                  `totalQuantityAfter` INT(10) NOT NULL,                                                               
                  `averageCostBefore` DOUBLE(10,2) NOT NULL,                                                           
                  `averageCostAfter` DOUBLE(10,2) NOT NULL,                                                            
                  `quantity` INT(10) NOT NULL,                                                                         
                  `costPrice` DOUBLE(10,2) NOT NULL,                                                                   
                  `transactionPrice` DOUBLE(10,2) NOT NULL,                                                            
                  `transactionEmployeeID` INT(2) NOT NULL,                                                             
                  `transactionDate` DATE NOT NULL,                                                                     
                  `transactionTime` TIME NOT NULL,                                                                     
                  `transactionType` ENUM('ti','to','tif','tof','i','r','e','f','p','s','g') NOT NULL,                  
                  PRIMARY KEY (`transactionID`,`transactionDetailsID`,`outletFromID`,`outletToID`,`transactionType`),  
                  KEY `NewIndex1` (`transactionDate`),                                                                 
                  KEY `NewIndex2` (`productID`)                                                                        
                ) ENGINE=INNODB DEFAULT CHARSET=latin1

[voidyman]

SELECT productID,totalAmountAfter,transactionDate 
FROM(
SELECT * from tbltransaction 
WHERE transactionDate BETWEEN '2010-01-01' AND '2010-12-31'
) A
having A.transactionDate =max(A.transactionDate )

Should work

[newbie14]

Dear Voidyman,
I run the query but it gave me empty results? Why ya?

[voidyman]

do you have data for this date range? try running the inner query alone(within the from)

[newbie14]

Dear Voidyman,
Yes I have confirm by running via the inner query there is data in that range?

[voidyman]

I dnt know newbie14.. I have run a similar query and it seems to work fine for me..

[newbie14]

Dear Voidyman,
Is very surprising to me why it works for you and not on my end. Anything else possible is my mistake?

[smantscheff]

Submit a complete test case - CREATE TABLE statements, INSERT statements and the query which does not work - and we will be able to track down the mistake.