Need help on distinct parameter

[bulgin]

Using Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2

 

 

I have the following bit of code:

 

SELECT distinct request_uri , sent_or_not_sent, id FROM web3_access_log where sent_or_not_sent = '0'

 

and the result set are 4 when I had hoped it would be just 1.

 

There are 4 records with the same request_uri but I only want returned 1 instance of that. In the above code the id's are all different and I'm sure that is why 4 rows are returned and not 1.

 

What would I do to make sure only the first occurrence of the request_uri is returned along with those other fields?

 

Much confused.

 

Thanks in advance.

 

[br0ken]

The distinct query applies to all of the fields selected so even though all of the rows contain the same 'request_uri', they will have different 'id' and 'sent_or_not_sent' values which will make the rows different and therefore distinct.

[bulgin]

Thanks I finally figured out that I need to put group by request_uri and just remove the distinct from the code and it works fine.

 

 

[fenway]

  Quote

Thanks I finally figured out that I need to put group by request_uri and just remove the distinct from the code and it works fine.

Careful -- you have other fields in your select list that aren't aggregrated!

[bulgin]

  Quote

  Quote

Thanks I finally figured out that I need to put group by request_uri and just remove the distinct from the code and it works fine.

Careful -- you have other fields in your select list that aren't aggregrated!

 

Okay, thanks.  I'm a newbie here so what are the implications with the aggregation?  The method I used seemed to work perfectly, it only returned 1 row with all the fields I needed.  Are you implying that there are some records floating around out there unhinged?

[fenway]

Post the final query & table structure and I'll tell you...

[bulgin]

Final query:

 

SELECT substring( web3_access_log.request_uri, 2 ) , web3_access_log.sent_or_not_sent, web3_access_log.id, web3_access_log.remote_host, cust.widget, cust.logged_in_user_email FROM web3_access_log JOIN cust ON substring( web3_access_log.request_uri, 2 ) = cust.widget where web3_access_log.sent_or_not_sent = '0' group by request_uri

 

table structure of web3_access_log:

id  char(19)  utf8_general_ci

remote_host varchar(50) utf8_general_ci

request_uri varchar(255) utf8_general_ci

sent_or_not_sent varchar(11) utf8_general_ci

 

table structure of cust:

widget varchar(255) utf8_general_ci

logged_in_user_email varchar(255) utf8_general_ci

 

 

The query returns only one occurence from many records of each request_uri with the assocaited fields.  For example the table web3_access_log may have:

 

request_uri:

asdfasd0980asd

-9-345assdfasdf

09808fawewfqw

-9-345assdfasdf

-9-345assdfasdf

-9-345assdfasdf

-9-345assdfasdf

 

The results are:

asdfasd0980asd ... along with fields from other table that match

-9-345assdfasdf... along with fields from other table that match

09808fawewfqw... along with fields from other table that match

 

am I missing something important here?

 

[fenway]

Yes, something very important... the values in the other columns can come from *ANY* of the aggregated rows -- not necessarily the same ones!

[bulgin]

wow! that is important.  How do I fix that?

 

Thanks for pointing that out I never would have known!

 

[fenway]

First you need to group by to find the single row per group that you're interested in -- then you need to join it back to the rest of the DB.

[bulgin]

The code I have above does group by, but when you say join back there I get lost.

[fenway]

  Quote

The code I have above does group by, but when you say join back there I get lost.

Forget about the other columns for a minute... just write the simplest group by.