CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Dmitriy Bilichenko
Dmitriy Bilichenko

Reputation: 43

MySQL sorting and grouping on last one

Hello i need help with my MYSQL query

i have table 

id | tn   | title    | customer_id |create_time | comment              |
1  | 1342 | sample1  | customer1   | 2012-01-01 | hello world          |
2  | 1342 | sample1  | customer1   | 2012-01-02 | hello world          |
3  | 1342 | sample1  | customer1   | 2012-01-03 | hello new world      |
4  | 3362 | sample2  | customer1   | 2012-01-02 | good bye world       |
5  | 3362 | sample2  | customer1   | 2012-01-03 | good bye world       |
6  | 3362 | sample2  | customer1   | 2012-01-04 | good bye world       |
7  | 3362 | sample2  | customer1   | 2012-01-05 | good bye new world   |

when i do group by tn i taked

1  | 1342 | sample1  | customer1   | 2012-01-01 | hello world          |
4  | 3362 | sample2  | customer1   | 2012-01-02 | good bye world       |

but i need to take 

3  | 1342 | sample1  | customer1   | 2012-01-03 | hello new world      |
7  | 3362 | sample2  | customer1   | 2012-01-05 | good bye new world   |

it's like grouping by tn with maximum id or maximum create_time

How can i do this? Thanks!

Upvotes: 1

Views: 88

Answers (3)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Try this: 

mysql> select * from ( select * from tbl2 tn order by id desc ) t group by tn;
+------+------+---------+-------------+-------------+--------------------+
| id   | tn   | title   | customer_id | create_time | comment            |
+------+------+---------+-------------+-------------+--------------------+
|    3 | 1342 | sample1 | customer1   | 2012-01-03  | hello new world    |
|    7 | 3362 | sample2 | customer1   | 2012-01-05  | good bye new world |
+------+------+---------+-------------+-------------+--------------------+
2 rows in set (0.02 sec)

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

Try this

Select t.* from 
table t right join
(Select max(id) as max_id from table group by tn) t1 on (t.id=t1.max_id)

Upvotes: 0

vearutop
vearutop

Reputation: 4072

SELECT t2.* FROM
(SELECT MAX(id) AS id,tn FROM my_table GROUP BY tn) AS t1
LEFT JOIN my_table AS t2 USING(id)

Upvotes: 1

Related Questions