mysql distinct..

[willg825]

Hi:

I am trying to run a query using 'distinct' but only for *one* field; i.e, one field in particular has lots of duplicates and I dont want any of the duplicates. However, distinct only seems to be able to apply to the entire query, i.e:

select distinct field1, field2 from table <-works fine

select field1, distinct(field2) from table <- this throws an error

is there a way to do this with distinct? thanks,

will

[Stu_Rogers]

I might be wrong but it sounds like the same problem I was having recently. See this thread...

[webmasterworld.com...]

If this doesn't help, please explain more fully what you are trying to achieve and why.

[willg825]

Stu.. thanks for the reply.. you are right that htis is what I am trying to do.. However, it is in the middle of a complicated search algorithm, so running (n) more statements (one for reach row) would be painfully slow. The second way is what I have setup now: i was just hoping there was a way to do it in one simple, nice mysql statement..

[arran]

select distinct field1, field2 from table

<-works fine

select field1, distinct(field2) from table

<- this throws an error

Hi Will,

Consider the following scenario - 'table' has 2 rows (1,3) and (2,3). If query 2 was valid, which row would be returned? It's nondeterministic - either row could be returned and that's why this query isn't valid.

If you aren't bothered about which row would be returned, you could hack it by using a 'group by':

select max(field1) from table group by field2

For example, this would return (2,3).

arran.

[willg825]

Yeah.... that makes sense. I just figured that it would return the first occurence of the row, and then throw out the rest (if i specified distinct for just one field). Tried the group by hack and it worked. Thanks a bunch--I appreciate it.

Will