mysql select only unique row

Question

I would like to select only unique row from the table, can someone help me out?

 SELECT * FROM table
 where to_user = ? 
 and deleted != ? 
 and del2 != ?
 and is_read = '0' 
  order by id desc


+----+-----+------+
| id | message_id |
+----+-----+------+
|  1 | 23         | 
|  2 | 23         | 
|  3 | 23         | 
|  4 | 24         | 
|  5 | 25         | 
+----+-----+------+

I need something like

+----+-----+------+
| id | message_id |
+----+-----+------+
|  3 | 23         | 
|  4 | 24         | 
|  5 | 25         | 
+----+-----+------+

So you only need the largest id for a particular message_id? — SWeko
– SWeko, Commented Nov 27, 2013 at 6:59
@SWeko if the message_id is similar I just want only the last one. — user3006683
– user3006683, Commented Nov 27, 2013 at 7:02
If by similar you mean same, and the last one is the largest one, then mine or Aziz's answers will do the trick. — SWeko
– SWeko, Commented Nov 27, 2013 at 7:04

Aziz Shaikh · Accepted Answer · 2013-11-27 06:59:07Z

4

Try this:

SELECT MAX(id), message_id
FROM tablename
GROUP BY message_id

and if you have other fields then:

SELECT MAX(id), message_id
FROM tablename
WHERE to_user = ? 
AND deleted != ? 
AND del2 != ?
AND is_read = '0'
GROUP BY message_id 
ORDER BY id DESC

answered Nov 27, 2013 at 6:59

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SWeko · Accepted Answer · 2013-11-27 07:00:54Z

1

If you only need the largest id for a particular message_id

SELECT max(id), message_id FROM table
 where to_user = ? 
 and deleted != ? 
 and del2 != ?
 and is_read = '0'
group by message_id 
  order by id desc

answered Nov 27, 2013 at 7:00

SWeko

30.9k11 gold badges74 silver badges107 bronze badges

Comments

Vijay Verma · Accepted Answer · 2013-11-27 07:18:14Z

-1

Try DISTINCT keyword. This will work definitely:

SELECT DISTINCT(message_id), id FROM table;

edited Nov 27, 2013 at 7:18

answered Nov 27, 2013 at 7:02

Vijay Verma

3,7082 gold badges21 silver badges27 bronze badges

Collectives™ on Stack Overflow

mysql select only unique row

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related