Problems with Grouping Statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Teg
Forum Newbie
Posts: 3
Joined: Thu Jun 20, 2002 4:04 pm

Problems with Grouping Statement

Post 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?
Teg
Forum Newbie
Posts: 3
Joined: Thu Jun 20, 2002 4:04 pm

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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?
Teg
Forum Newbie
Posts: 3
Joined: Thu Jun 20, 2002 4:04 pm

Post 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?
Post Reply