Problems with Grouping Statement
Posted: Thu Jun 20, 2002 4:04 pm
Code: Select all
CREATE TABLE purchases (
OrderID int(10) unsigned NOT NULL auto_increment,
Comment tinytext NOT NULL,
GroupID int(10) unsigned NOT NULL default '0',
CustID int(10) unsigned NOT NULL default '0',
CustName varchar(50) NOT NULL default '',
DatePurchased int(11) unsigned NOT NULL default '0',
PRIMARY KEY (OrderID),
KEY GroupID (GroupID),
KEY CustID (CustID),
KEY DatePurchased (DatePurchased)
);Here's the situation I'm in. I need to grab the latest
CustID,DatePurchased,CustName,GroupID,OrderID and comment from table
purchases Grouped by their GroupID.
I was was working with a query like this:
Code: Select all
SELECT *
FROM purchases
WHERE GroupID IN (1,3,5)
GROUP BY GroupID
ORDER BY OrderID DESC;statement, the first instead of the last OrderID.
The where clause is for me to check in on certain groups via a
webform. I'm also making this much simpler than it is since I'm having
to JOIN this with a few other tables in my regular query.
I'm a SQL noob so does anyone have any suggestions how I can get the
last order from each group?
I know it's something as simple as a HAVING clause or changing the SELECT statement to include a DISTINCT, but I can't figure how. Any suggestions?