CODEWITHSUNDEEP
sqlmysql
Babiker
Babiker

Reputation: 18798

How can I select an entire row without selecting rows where a certain column value is duplicated?

How can I select an entire row without selecting rows where a certain column value is duplicated?

EDIT:

Sorry my question is a bit vague. Assuming i have a table with two columns: names and score. There are duplicate values in names. I want to select distinct names and also select their scores.

Upvotes: 1

Views: 424

Answers (4)

Nick Rolando
Nick Rolando

Reputation: 26167

Try using the DISTINCT clause on the columns you don't want duplicates of.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Based on the edited information given, this will use the GROUP_CONCAT function to produce the distinct names and a comma-delimited list of scores. If more appropriate, you could substitute another aggregate function (e.g., MIN, MAX, SUM) for the GROUP_CONCAT.

SELECT Name, GROUP_CONCAT(Score)
    FROM YourTable
    GROUP BY Name

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

Using the following example data...

name   score
-----  -----
James     10
James     12
Lisa      45
John      42

...the following queries should return the third and fourth row.

select name, score
  from table
 where name in(select name 
                 from table
                group by name having count(*) = 1);

...less clear, but probable more efficient on MySQL.

select t1.name, t1.score
  from (select name 
          from table
         group by name having count(*) = 1
       ) t1
  join table t2 on(t1.name = t2.name)

Upvotes: 1

KM.
KM.

Reputation: 103587

based on your latest edit, try this:

select 
    name, SUM(score) AS TotalScore
    from YourTable
    group by name

Upvotes: 0

Related Questions