Way to use DISTINCT in a query, where I wanna get other fields too?

[youradds]

Hi,

I'm currently doing:

SELECT * FROM Clients_Pool WHERE client_offer_region_id = 310 ORDER BY RAND() LIMIT 3

However, there is a field in there called client_detail_id_fk.

How, we wanna really only get 1 record per advertiser ... but I'm a little unsure how to do this.

This would obviously get distinct client_ids, but we actually need a record to go with it:

SELECT DISTINCT(client_detail_id_fk) FROM Clients_Pool WHERE client_offer_region_id = 310 ORDER BY RAND() LIMIT 3

Is this even possible? Or shall I just make the LIMIT a bit higher, and then use my perl code to make sure it only adds advertisers offers which have not already been put into the loop?

TIA

Andy

 

[youradds]

I'm assuming this isn't possible then?

 

[r937]

it's possible, but you haven't given enough information

~which~ record do you want for each client?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

Thanks for the reply

Ok, lets say the table has this structure:

detail_id   offer_id  offer_title       country_id 
123            1          some test       1
223            2          some test 2     1
223            3          some test 3     1
123            4          some test 4     1

What I would like to try and do, is get the following records from there:

123            1          some test       1
223            2          some test 2     1

i.e only 1 offer per distinct detail_id

TIA!

Andy

 

[r937]

from that example, it looks like you've answered the question "which row" with "the one with the lowest offer_id"

SELECT t.detail_id   
     , t.offer_id  
     , t.offer_title       
     , t.country_id 
  FROM ( SELECT detail_id
              , MIN(offer_id) AS lowest_offer
           FROM daTable
         GROUP
             BY detail_id ) AS m
INNER
  JOIN daTable AS t
    ON t.detail_id = m.detail_id
   AND t.offer_id = m.lowest_offer

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

Thanks for the reply

We actually randomly generate the list (basically, its a "pool" table, with all the offers in - and we just wanna randomly grab one, but ONLY if the "advertiser" hasn't already had a record grabbed)

I'm assuming the above query wouldn't do that?

TIA

Andy

 

[r937]

I'm assuming the above query wouldn't do that?

you are correct, it would not "randomly grab"


i tried to match the solution to the information you gave

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

Thats cool

I just added ORDER BY RAND() at the end, and it seems to work fine. No obvious issues you can see with doing it that way?

Cheers

Andy

 

[r937]

No obvious issues you can see with doing it that way?

it won't ensure only one row per advertiser

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Ah ok

Maybe I'll just stick with the code I currently have, and then instead of just fetching 5 rows, get it to grab 10 - this should hopefully ensure I get enough adverts to fill the spaces

Thanks again for your help though

Cheers

Andy