Page 1 of 1

Problems with Grouping Statement

Posted: Thu Jun 20, 2002 4:04 pm
by Teg

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)
);
Above is the SQL dump of something I've been playing with.
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;
However, this does not work. It returns, because of the GROUPing
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?

Posted: Thu Jun 20, 2002 9:38 pm
by Teg
Let me clarify this a bit more by giving some sample data.

Code: Select all

mysql> SELECT * FROM purchases;
+---------+---------+--------+----------------+---------------+
| OrderID | GroupID | CustID | CustName       | DatePurchased |
+---------+---------+--------+----------------+---------------+
|       1 |       1 |      1 | John Doe       |    1024625553 |
|       2 |       3 |      2 | Jane Doe       |    1024625608 |
|       3 |       1 |      3 | Linus Torvalds |    1024625686 |
|       4 |       1 |     15 | Perl O'Reilly  |    1024625759 |
|       5 |       1 |      6 | Matt Daemon    |    1024625812 |
|       6 |       3 |      8 | Last Buyer     |    1024625859 |
|       7 |       2 |     14 | Miss Thang     |          1969 |
|       8 |       2 |     12 | Lil Debbie     |    1024626325 |
+---------+---------+--------+----------------+---------------+
8 rows in set (0.00 sec)
Here is a sample query:

Code: Select all

mysql> SELECT GroupID,CustID,CustName
    -> FROM purchases
    -> GROUP BY GROUPID
    -> ORDER BY GroupID,OrderID DESC;
+---------+--------+------------+
| GroupID | CustID | CustName   |
+---------+--------+------------+
|       1 |      1 | John Doe   |
|       2 |     14 | Miss Thang |
|       3 |      2 | Jane Doe   |
+---------+--------+------------+
3 rows in set (0.00 sec)
This is obviously not what I had expected. This is the data I expect to grab:

Code: Select all

+---------+--------+-------------+
| GroupID | CustID | CustName    |
+---------+--------+-------------+
|       1 |      6 | Matt Daemon |
|       2 |     12 | Lil Debbie  |
|       3 |      8 | Last Buyer  |
+---------+--------+-------------+
It seems simple, but I'm not seeing the solution. I've tried lots of things and none really seem to work.

Posted: Fri Jun 21, 2002 4:15 am
by mikeq
Group doesn't work like this.

Group is used to 'GROUP' like data together, because you have CustName and CustID in your result it can't group on GroupID because CustName and CustID are unique (not repeated in your data).
This is obviously not what I had expected
I'm sorry there is nothing obvious about what you are trying to get.

Are you trying to get the Last purchase made within each GroupID?

Posted: Fri Jun 21, 2002 10:20 am
by Teg
mikeq wrote:Group doesn't work like this.

Group is used to 'GROUP' like data together, because you have CustName and CustID in your result it can't group on GroupID because CustName and CustID are unique (not repeated in your data).
After reading up on this at mysql.com's docs, I came to the same conclusion, but I'm still trying to find a way of getting the information I need 'grouped by' groupid.
mikeq wrote:Are you trying to get the Last purchase made within each GroupID?
Yes and perhaps by GroupIDs that I can specify in the WHERE clause.

Any ideas? Is it possible?