Help with DISTINCT query

[c4n]

Hello,
I'm having some troubles with a DISTINCT query. Here is my problem:

I have a table called "test" with collums "a", "b" and "c" and some different values (integers) in them.

Example:
a | b | c
---------
1 | 2 | 3
1 | 2 | 2
1 | 3 | 2
2 | 2 | 2
3 | 2 | 2
3 | 3 | 3
1 | 2 | 3

Notice that many rows have 1 in collum "a". Now I want to get only 1 (ONE) result with collum a set to 1, and one with collum "a" set to 2, and one with collum "a" set to 3 etc. Other collums ("b","c") don't matter as long as I get only one row with collum "a" set to a value.

So from the above table I want these values:
1 2 3
2 2 2
3 2 2

What SQL query to use? I tried

SELECT DISTINCT `a`, `b` , `c`
FROM `test`
WHERE 1
LIMIT 0 , 30

but it returns
1 | 2 | 3
1 | 2 | 2
1 | 3 | 2
2 | 2 | 2
3 | 2 | 2
3 | 3 | 3

Thanks in advance!

 

[TonyGroves]

How about:
[tt]select * from t group by a[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[c4n]

Works like a charm, thanks!

 

[r937]

the solution that tony gave you works only in mysql

in all other databases it would give you an immediate syntax error

the results are unpredictable and you might want to give some thought to that

see 13.9.3 GROUP BY with Hidden Fields --

MySQL extends the use of GROUP BY so that you can use columns or calculations in the SELECT list that don't appear in the GROUP BY clause. This stands for any possible value for this group... Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results.

emphasis by mysql, not me

rudy

http://r937.com/

SQL Consulting

 

[TonyGroves]

Rudy -

My reading of that article suggests that a random record from each group will be returned, which is exactly what C4N wants.

In my own situation, if it's down to a choice between compact and efficient MySQl-specific syntax, and some slow and convoluted query which conforms to all the relevant ISO standards, I know which I'd choose ... unless I was planning to change my database system soon.

-----
ALTER world DROP injustice, ADD peace;

 

[r937]

true enough

until the day mysql tightens up its syntax rules to conform to standards

tick tick, tick tick, tick tick...



rudy

http://r937.com/

SQL Consulting