tricky sql (for a n00b) 2

[Sniipe]

I have this sql

SELECT    
  max(d.distributorid),
  c.countryid
FROM    
  distributor d,    
  country c    
WHERE    
  d.countryid = c.countryid    
  and d.hassmbsb=1    
group by    
    c.countryid

Essentially there are a number of distributors per country. Up till now the latest distributor added (distributorid is an autonumber) was guranateed to have the latest version number. and the above code worked

now new distributors are being added but who's version number is lower.

EG
The code above would bring back this view:

Distributorid | CountryID
10000           1                {this version is 7.3}
6662            2                {this version is 7.3}
1232            3                {this version is 7.2}

Before you would be guaranteed that the version number on these distributor would be the latest/current version.

BUT now someone is coming along and adding in new distributors who are based off older versions and its screwing up the view

(new distributor added)
distributorid=10001
country=3
version=7.1

Distributorid | CountryID
10000           1             {this d.version is 7.3}
6662            2             {this d.version is 7.3}
10001           3             {this d.version is 7.1}

So I would prefer country ID 3 to use the distributor with the latest version number of any distributors in that country (I don't care which of the many distributors it is as long as its one with the latest version in that country - in this case it would be distributor 1232 who's version is 7.2 instead of 10001)

 

[r937]

try this (untested) --

SELECT MAX(d.distributorid)
     , c.countryid
  FROM country AS c    
INNER
  JOIN ( SELECT countryid
              , MAX(version) AS maxversion
           FROM distributor
          WHERE hassmbsb = 1 
         GROUP
             BY countryid ) AS m
    ON m.countryid = c.countryid
INNER
  JOIN distributor AS d
    ON d.countryid = c.countryid    
   AND d.hassmbsb = 1
   AND d.version = m.maxversion    
GROUP 
    BY c.countryid

r937.com | rudy.ca

 

[Sniipe]

looking good, looking very good. I'll load it up on monday and let u know, but I did a quick test and it comes back with what I expect. Thanks

 

[Sniipe]

Thanks r937,

The code worked perfectly. I have however come up with this statement:

SELECT MAX(d.distributorid) as distributorid, c.countryid
FROM country c
inner join distributor d on d.countryid = c.countryid
where d.version != '7.4' and d.hassmbsb = 1
group by c.countryid

As I wanted to get all the versions except 7.4
Unfortunately it didn't fit in with your code.

 

[r937]

Unfortunately it didn't fit in with your code."

i'm sorry, i do not understand what you mean

are you happy with your query?

r937.com | rudy.ca

 

[PHV]

SELECT MAX(d.distributorid)
     , c.countryid
  FROM country AS c    
INNER
  JOIN ( SELECT countryid
              , MAX(version) AS maxversion
           FROM distributor
          WHERE [!]version != '7.4' and [/!]hassmbsb = 1 
         GROUP
             BY countryid ) AS m
    ON m.countryid = c.countryid
INNER
  JOIN distributor AS d
    ON d.countryid = c.countryid    
   AND d.hassmbsb = 1
   AND d.version = m.maxversion    
GROUP 
    BY c.countryid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sniipe]

My bad, had the version != '7.4' in the inner join