CODEWITHSUNDEEP
phpmysql
David19801
David19801

Reputation: 11448

mysql distinct with other things too

This should be very simple, but I cannot find the correct syntax using search.

I have:

SELECT distinct name, id FROM table1 WHERE length<6;

I want to return the id's and names of all distinct names (not distinct ids, since thats an auto increment anyway), but this query is currently returning all names and ids, not just the distinct names...

What's the correct way to do this?

Edit: Length is another columns name and not relevant specifically here.

Upvotes: 1

Views: 114

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Distinct names:

SELECT DISTINCT name 
FROM table1 
WHERE length < 6

Distinct names and all their ids:

SELECT name, GROUP_CONCAT(id) 
FROM table1 
WHERE length < 6
GROUP BY name

Distinct names and the lowest id:

SELECT name, MIN(id)              --- or MAX(id) for the highest id
FROM table1 
WHERE length < 6
GROUP BY name

Distinct names and a (more or less) random id (this works in MySQL only and no other DBMS):

SELECT name, id            
FROM table1 
WHERE length < 6
GROUP BY name

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270617

So it sounds like you want distinct names, and any associated id. DISTINCT will return distinct rows as combinations of the selected columns, and since id is auto-increment, that means all rows. Instead use an aggregate.

This query will return the name, and the first auto-increment id with it:

SELECT
  name, 
  MIN(id) as id
FROM table1
WHERE length < 6
GROUP BY name

Upvotes: 2

dklt
dklt

Reputation: 1700

Maybe you mean 

where length(name) < 6

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10469

select id, name from table1 as t0 join ( select distinct name from table1 ) as t1 on t0.name = t1.name;

Upvotes: 0

Related Questions