MySQL Query Help 2

[epandi]

Hi Everyone.

I have the following situation:

TABLE: order_memo
mysql> SELECT * FROM
`order_memo` ORDER BY `order_id` LIMIT 10;
+----------+---------+---------------------+-----------+-----------+
| order_id | memo_id | memo_time           | status_id | memo_read |
+----------+---------+---------------------+-----------+-----------+
|        1 |       2 | 2005-01-26 02:26:15 |         1 |         2 |
|        1 |       1 | 2005-01-26 02:24:53 |         0 |         2 |
|        1 |       0 | 2005-01-25 11:57:41 |         0 |         1 |
|        2 |       1 | 2005-01-26 02:28:29 |         1 |         2 |
|        2 |       0 | 2005-01-25 18:38:15 |         0 |         1 |
|        3 |       0 | 2005-01-25 18:39:34 |         0 |         1 |
|        3 |       1 | 2005-01-27 04:02:52 |         1 |         2 |
|        4 |       0 | 2005-01-25 18:42:03 |         0 |         1 |
|        4 |       1 | 2005-01-27 04:04:34 |         1 |         2 |
|        5 |       0 | 2005-01-25 18:43:37 |         0 |         1 |
+----------+---------+---------------------+-----------+-----------+

What i need is the following, and i can't get the right sql command for that.

I need a distinct on the field `order_id` with the maximum value of `memo_id` so i need the output:

+----------+---------+---------------------+-----------+-----------+
| order_id | memo_id | memo_time           | status_id | memo_read |
+----------+---------+---------------------+-----------+-----------+
|        1 |       2 | 2005-01-26 02:26:15 |         1 |         2 |
|        2 |       1 | 2005-01-26 02:28:29 |         1 |         2 |
|        3 |       1 | 2005-01-27 04:02:52 |         1 |         2 |
|        4 |       1 | 2005-01-27 04:04:34 |         1 |         2 |
|        5 |       0 | 2005-01-25 18:43:37 |         0 |         1 |
+----------+---------+---------------------+-----------+-----------+

Is there anybody who can help me with this?

 

[Bastien]

try

SELECT distinct(order_id),* FROM
`order_memo`
order by order_id asc, memo_id desc
LIMIT 10;

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[epandi]

SELECT DISTINCT (

order_id
), *
FROM `order_memo`
ORDER BY order_id ASC , memo_id DESC
LIMIT 10

MySQL retourneerde:


You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '*
FROM `order_memo`
ORDER BY order_id ASC , memo_id DESC
L

 

[Bastien]

what version mysql?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[epandi]

MySQL 4.0.23

Thanks for the quick reply's.

 

[Bastien]

try listing the fields

select distinct(order_id),memo_id ,memo_time, status_id, memo_read
FROM order_memo
ORDER BY order_id ASC , memo_id DESC
LIMIT 10

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[epandi]

Hi Bastien,

Here is my SQL Result.

mysql> select distinct(order_id),memo_id ,memo_time, status_id, memo_read
    -> FROM order_memo
    -> ORDER BY order_id ASC , memo_id DESC
    -> LIMIT 10;
+----------+---------+---------------------+-----------+-----------+
| order_id | memo_id | memo_time           | status_id | memo_read |
+----------+---------+---------------------+-----------+-----------+
|        1 |       2 | 2005-01-26 02:26:15 |         1 |         2 |
|        1 |       1 | 2005-01-26 02:24:53 |         0 |         2 |
|        1 |       0 | 2005-01-25 11:57:41 |         0 |         1 |
|        2 |       1 | 2005-01-26 02:28:29 |         1 |         2 |
|        2 |       0 | 2005-01-25 18:38:15 |         0 |         1 |
|        3 |       1 | 2005-01-27 04:02:52 |         1 |         2 |
|        3 |       0 | 2005-01-25 18:39:34 |         0 |         1 |
|        4 |       1 | 2005-01-27 04:04:34 |         1 |         2 |
|        4 |       0 | 2005-01-25 18:42:03 |         0 |         1 |
|        5 |       1 | 2005-01-27 04:05:49 |         1 |         2 |
+----------+---------+---------------------+-----------+-----------+

 

[r937]

i don't know why all you guys write

select distinct(foo), bar, ...

DISTINCT is not a function

DISTINCT applies to all columns selected

epandi, if you get the max(memo_id) for each order_id, is that enough? do you really need any other columns? and if so, why? just curious

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[Bastien]

try adding the GROUP BY order_ID clause

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[epandi]

Yeah i need the max memo_id from every order_id, that's becasuse i need to make a mailing every night to the admins, office and manufactors (where the order_id is assigned to) so they know the last status (at the last memo_id).

 

[epandi]

Thanks for all the help guys! I finally have the correct SQL statement:

mysql> select distinct order_id, max(memo_id), memo_time, status_id, memo_read
    -> FROM order_memo
    -> GROUP BY order_ID
    -> ORDER BY order_id ASC , memo_id DESC
    -> LIMIT 10 ;
+----------+--------------+---------------------+-----------+-----------+
| order_id | max(memo_id) | memo_time           | status_id | memo_read |
+----------+--------------+---------------------+-----------+-----------+
|        1 |            2 | 2005-01-26 02:26:15 |         1 |         2 |
|        2 |            1 | 2005-01-26 02:28:29 |         1 |         2 |
|        3 |            1 | 2005-01-25 18:39:34 |         0 |         1 |
|        4 |            1 | 2005-01-25 18:42:03 |         0 |         1 |
|        5 |            1 | 2005-01-25 18:43:37 |         0 |         1 |
|        6 |            1 | 2005-01-25 18:45:24 |         0 |         1 |
|        7 |            1 | 2005-01-25 18:46:32 |         0 |         1 |
|        8 |            2 | 2005-01-26 09:11:47 |         0 |         1 |
|        9 |            1 | 2005-01-26 09:17:52 |         0 |         1 |
|       10 |            1 | 2005-01-26 09:20:42 |         0 |         1 |
+----------+--------------+---------------------+-----------+-----------+
10 rows in set (0.01 sec)

Thanks for all the help!

 

[r937]

i'm sorry, you may have a query that appears to work, but it isn't "correct" because the GROUP BY clause does not contain all the non-aggregate columns in the SELECT

plus, DISTINCT is redundant with GROUP BY (used properly)

please read 12.9.3. GROUP BY with Hidden Fields

Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results.

if you should ever find yourself in a position of writing sql for any other database besides mysql, you will need to know how to do it correctly, because it will throw an error the way you have it now

not trying to be picky, just interested in helping you achieve the results you want

if memo_time, status_id, memo_read all have exactly the same values for each row within their group, you're okay

otherwise you will get unpredictable results




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)