"DISTINCT", but distinct what?

[mitsubishi]

Hi, I've been looking all over but can't find a clear answer.

SELECT DISTINCT a,b,c ...etc...

What is distinct? 'a' or the entire rowset?

Also is this comptible on mSQL, access, oracle and postgres as well as mySQL?

[MattR]

DISTINCT row. If you want a distinct set of columns you can try 'group by' but it works only in certain circumstances.

[mitsubishi]

So, the whole row is distinct, not just individual columns.

+---------+---------+---------+
|    a    |    b    |    c    |
+---------+---------+---------+
|    1    |    abc  |   34    |
|    1    |    abc  |   21    | < this will be returned
|    1    |    abc  |   21    | < but this wont?
|    2    |    abc  |   34    |
+---------+---------+---------+

And

SELECT DISTINCT a,b,c FROM table ORDER BY a,b

Is not going to fetch me the 2nd and 4th rows?

[MattR]

Re: first question. Yes, it will strip out duplicate rows.

Re: Second query -- no, you're ORDERing BY, not GROUPing by. If you were to group by the three columns it would/should imitate the distinct. Of course, it probably won't work without an aggregate function tho.

[mitsubishi]

Right, cheers Matt, the mySQL manual isn't terribly helpful with a lot of things, I suppose it assumes you know SQL already.

Better go and read up on 'GROUP BY' it's the last major thing I havn't learnt yet.

Now I just need to find some info on cross-compatible syntax.