MySQL Forums
Forum List  »  InnoDB

tricky grouping problem
Posted by: michael keough
Date: October 28, 2008 10:39AM

i have a field called lot code. lot codes are in the following format
12345AB5 12345678

i need to get a group of these lotcodes by
12345AB5 1234

the 5678 represents a minute value and will distort the report i am generrating

here is the sql i have been trying

SELECT a.lotcode, count( a.lotcode ) AS total
FROM information a
WHERE SUBSTRING( a.lotcode, 0, 12) = SUBSTRING( a.lotcode, 0, 12)
AND a.date_taken > '2008-10-01'
GROUP BY SUBSTRING( a.lotcode, 0, 12) DESC
ORDER BY `total` DESC
LIMIT 10

aka i need to get all lotcodes from the system where the 'partial' lotcode is the same and then group them all by the 'partial' lotcode

thanks for any help given

Options: ReplyQuote


Subject
Views
Written By
Posted
tricky grouping problem
2550
October 28, 2008 10:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.