SQL DISTINCT question

MattF · 2009-01-21 02:03:47

Would the following:

'SELECT DISTINCT client_ip, client_key, port FROM '

apply to all of the columns listed, or just the first column after DISTINCT?

Cheer.

elbekko · 2009-01-21 02:11:05

The first one (unless I'm somehow horribly mistaken ).

MattF · 2009-01-21 03:01:56

I was hoping that wouldn't be the answer. Just trying to find a way to achieve the PgSQL method but without the MySQL incompatible syntax:

'SELECT DISTINCT ON (client_ip, client_key, port) client_ip FROM '

Back to the drawing board then.

Smartys · 2009-01-21 03:08:27

I'm pretty sure it applies to all columns: it looks for identical rows according to the documentation

MattF · 2009-01-21 05:38:02

Sweet. That'll make things a lot easier then. I must admit, I'd interpreted it the same way as Elbekko, that only the first column applied.

horus · 2009-01-21 07:46:54

I don't know PgSQL but in other db (I know quite well oracle and mysql) distinct is applied to all column (as sayd by Smartys) and ensure that there is no duplicate rows. I think that all db engine do the same.

elbekko · 2009-01-21 12:12:38

Hrmm, I must've remembered wrong then, my bad

php.web · 2009-02-04 13:55:58

select distinct T.FLD1,T.FLD2, T.FLD3 workds fine for me.

MattF · 2009-02-04 15:05:01

Cheers for the advice on this one gents. I had completely misinterpreted the documentation on it. I can alter that query now without wondering whether it's going to be less selective or not.

Thanks again.

MattF · 2009-03-23 16:39:09

It would appear that elbekko and I were correct. Just been working on something totally unrelated, but if I put a COUNT(DISTINCT([first column])) on a similar query, the COUNT can be drastically different to the overall number of rows returned. It would appear, (unless I've missed some point completely), to work on the first column only in MySQL. Any additional columns make it return all results matching all criteria.

The intention is to only return results where the id, (first column), is unique. Adding extra columns just returns all matches which incorporate the extra columns whether the id is distinct or not.

Last edited by MattF (2009-03-23 16:42:10)

Smartys · 2009-03-23 17:20:28

SELECT DISTINCT is not the same as SELECT COUNT(DISTINCT col_name)).

http://dev.mysql.com/doc/refman/5.0/en/ … ation.html
Check out the equivalent group by statement they mention.

http://dev.mysql.com/doc/refman/5.0/en/ … t-distinct
As you can see, very different

MattF · 2009-03-23 17:40:21

It demonstrates the results difference nicely though.

Doing the query both ways returns a different result.

For example, the particular thing I'm working on at the moment:

SELECT DISTINCT id1 FROM [tablename] WHERE id1=1;

returns 890 rows.

SELECT DISTINCT id1, id2, id3 FROM [tablename] WHERE id1=1;

returns 899 rows. There are nine rows where id1 isn't distinct.

MattF · 2009-03-23 17:48:10

Damn. It needs the GROUP BY clause as well. It was in one of those links you posted Smartys. Cheers.

This query does return the expected result:

SELECT DISTINCT id1, id2, id3 FROM [tablename] WHERE id1=1 GROUP BY id1;

Edit: Just for future reference.

In MySQL, it appears to return the expected result with just the GROUP BY. The use of DISTINCT appears moot.

In PgSQL, it appears to need both GROUP BY and DISTINCT, literally as above. Something along the lines of:

SELECT DISTINCT id1 FROM [tablename] WHERE id1=1 GROUP BY id1, id2, id3;

Last edited by MattF (2009-03-23 18:23:30)

elbekko · 2009-03-23 18:34:13

pgSQL is annoying when it comes to group by (and having, and ...).

MattF · 2009-03-23 18:43:48

elbekko wrote:

pgSQL is annoying when it comes to group by (and having, and ...).

It's quirky, I'll definitely give it that. On that note, I've just been playing around a bit more, and to return multiple columns whilst keeping the id1 distinct, the query needs to be similar to:

SELECT DISTINCT DISTINCT ON (id1) id1, id2, id3 FROM [tablename] WHERE id1=1 GROUP BY id1, id2, id3;

Every variation one tries returns completely different results.

This one only appears to work correctly if you're returning a single column:

SELECT DISTINCT id1 FROM [tablename] WHERE id1=1 GROUP BY id1, id2, id3;

If you try it as:

SELECT DISTINCT id1, id2, id3 FROM [tablename] WHERE id1=1 GROUP BY id1, id2, id3;

you lose the exclusivity, hence the ON statement when returning more than one column. Well, at least if anyone comes across this thread in the future, they should have the answer to work with.

Forums

#1 2009-01-21 02:03:47

SQL DISTINCT question

#2 2009-01-21 02:11:05

Re: SQL DISTINCT question

#3 2009-01-21 03:01:56

Re: SQL DISTINCT question

#4 2009-01-21 03:08:27

Re: SQL DISTINCT question

#5 2009-01-21 05:38:02

Re: SQL DISTINCT question

#6 2009-01-21 07:46:54

Re: SQL DISTINCT question

#7 2009-01-21 12:12:38

Re: SQL DISTINCT question

#8 2009-02-04 13:55:58

Re: SQL DISTINCT question

#9 2009-02-04 15:05:01

Re: SQL DISTINCT question

#10 2009-03-23 16:39:09

Re: SQL DISTINCT question

#11 2009-03-23 17:20:28

Re: SQL DISTINCT question

#12 2009-03-23 17:40:21

Re: SQL DISTINCT question

#13 2009-03-23 17:48:10

Re: SQL DISTINCT question

#14 2009-03-23 18:34:13

Re: SQL DISTINCT question

#15 2009-03-23 18:43:48

Re: SQL DISTINCT question

Board footer