Mysql select distinct

Question

I am trying to select of the duplicate rows in mysql table it's working fine for me but the problem is that it is not letting me select all the fields in that query , just letting me select the field name i used as distinct , lemme write the query for better understading

mysql_query("SELECT DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

mysql_query("SELECT * , DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

1st one is working fine

now when i am trying to select all fields i am ending up with errors

i am trying to select the latest of the duplicates let say ticket_id 127 is 3 times on row id 7,8,9 so i want to select it once with the latest entry that would be 9 in this case and this applies on all the rest of the ticket_id's

Any idea thanks

The second query is just wrong. Explain what task you're trying to solve — zerkms, Aug 30 '11 at 22:32
What other fields are you trying to return? In the event a column has multiple values for a particular `ticket_id` what should happen then? — Martin Smith, Aug 30 '11 at 22:34
let say ticket_id 127 is 3 times on row id 7,8,9 so i want to select it once with the latest entry that would be 9 in this case and this applies on all the rest of the ticket_id's ...Any idea now – — Shanon, Aug 30 '11 at 22:43
[You can use one of the techniques in the answers here](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — Martin Smith, Aug 30 '11 at 22:44
Follow the greatest-n-per-group tag. This question as you give it in the comment has been answered dozens of times on Stack Overflow. — Bill Karwin, Aug 30 '11 at 22:46

score 78 · Answer 1 · edited Jun 20 '20 at 09:12

DISTINCT is not a function that applies only to some columns. It's a query modifier that applies to all columns in the select-list.

That is, DISTINCT reduces rows only if all columns are identical to the columns of another row.

DISTINCT must follow immediately after SELECT (along with other query modifiers, like SQL_CALC_FOUND_ROWS). Then following the query modifiers, you can list columns.

RIGHT: SELECT DISTINCT foo, ticket_id FROM table...

Output a row for each distinct pairing of values across ticket_id and foo.
WRONG: SELECT foo, DISTINCT ticket_id FROM table...

If there are three distinct values of ticket_id, would this return only three rows? What if there are six distinct values of foo? Which three values of the six possible values of foo should be output?
It's ambiguous as written.

a1ex07 · Accepted Answer · 2011-08-30T22:45:12.123

43

Are you looking for "SELECT * FROM temp_tickets GROUP BY ticket_id ORDER BY ticket_id ?

UPDATE

SELECT t.* 
FROM 
(SELECT ticket_id, MAX(id) as id FROM temp_tickets GROUP BY ticket_id) a  
INNER JOIN temp_tickets t ON (t.id = a.id)

edited Aug 30 '11 at 22:45

answered Aug 30 '11 at 22:34

a1ex07

36,826
12
90
103

that works but i am trying to select the latest of the duplicates let say ticket_id 127 is 3 times on row id 7,8,9 so i want to select it once with the latest entry that would be 9 in this case and this applies on all the rest of the ticket_id's ...Any idea now – Shanon Aug 30 '11 at 22:39

score 9 · Answer 3 · edited Aug 24 '14 at 18:42

9

You can use group by instead of distinct. Because when you use distinct, you'll get struggle to select all values from table. Unlike when you use group by, you can get distinct values and also all fields in table.

edited Aug 24 '14 at 18:42

Brad Larson

170,088
45
397
571

answered Feb 04 '14 at 04:20

John

723
1
10
12

score 3 · Answer 4 · edited Aug 06 '20 at 15:48

3

You can use DISTINCT like that

mysql_query("SELECT DISTINCT(ticket_id), column1, column2, column3 
FROM temp_tickets 
ORDER BY ticket_id");

edited Aug 06 '20 at 15:48

Siva

1,481
1
18
29

answered Dec 13 '13 at 20:48

Rahul Sharma

622
7
25

score 0 · Answer 5 · answered Aug 30 '11 at 22:34

0

use a subselect:

http://forums.asp.net/t/1470093.aspx

answered Aug 30 '11 at 22:34

Nicolas Modrzyk

13,961
2
36
40

Mysql select distinct

5 Answers5

Linked