CODEWITHSUNDEEP
mysqlsqldistinctrows
PaxBin
PaxBin

Reputation: 111

MYSQL - SELECT all rows, but use Distinct on one column

I have a table data like this

Column_a  | Column_b
  a       |    5
  b       |    25
  g       |    14
  t       |    13
  b       |    15
  c       |    04
  g       |    15
  b       |    13

in the column_a i have a lot of duplicate values, i want to be able to select all the rows from the table but if two rows have the same column_a value, i want only the row with the biggest value from colum_b in the results exemple of the result that i am looking for :

Column_a  | Column_b
  a       |    5
  b       |    25
  t       |    13
  c       |    04
  g       |    15

Thank you in advance

**

  1. Update of the question

**

these are the columns i have in my table : CRMID | user | ticket_id | | description | date | hour

what i am trying to do is to select all the rows from the table, but when two rows have the same ticket_id, i want only the newest one to appear in the results, so the row with the newest date and hour , Sorry for making this such complicated ! i am not a native english speaker and i find it hard to well explain the problem.

Upvotes: 2

Views: 2989

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you want to get all the columns in the table, then you have a different problem (and one not in the original posting). One reason you should add code into such a question is so you get a broader range of answers. I, for one, ignored the question, thinking it was just a newbie asking about obvious SQL functionality.

In MySQL the best approach is to use not exists:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.column_a = t.column_a and
                        t2.column_b > t.column_b
                 );

For optimal performance, you want an index on table(column_a, column_b). Also, this can return multiple rows, if there are duplicated maximum values.

This query is not intuitive. What it is doing is: "Get me all rows from the table where there is no other row with the same column_a value and a higher column_b value". If you think about it, this is the same as getting the maximum value. This has better performance than other methods (notably, aggregation and join), because MySQL does a simple index lookup for each row in the table. That is faster than aggregation and join.

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

select * from (select * from yourtable order by column_b desc)t group by column_a

Upvotes: -1

Siyual
Siyual

Reputation: 16917

You're looking for a Group By clause. Your syntax should look similar to this:

SELECT Column_A, MAX(Column_B) 
FROM Table
GROUP BY Column_A

Upvotes: 2

mleko
mleko

Reputation: 12233

SELECT Column_A, MAX(Column_B) FROM table
GROUP BY Column_A

Upvotes: 3

juergen d
juergen d

Reputation: 204746

If you group by column_a then you can use aggregate functions like max() on it to get the maximum value of each group

select column_a, 
       max(column_b) as column_b
from your_table
group by column_a

Upvotes: 4

Related Questions