SQLTeam.com | Weblogs | Forums

Help with inner join on Top select


#1

I have a sample database based on 3 tables (Sales, Products and Area). Sales show values of products sold each month in an area
AREA
AreaCode AreaDesc
1 UK
2 GERMANY
3 FRANCE
4 ITALY
5 SPAIN

**PRODUCT**
ProdCode        ProdDesc
100                  TVS
101                  COMPUTERS
102                   MOBILES

**SALES**
SaleMonth	Prod_fk	Area_fk	Value
Sep-17             100          3	
Sep-17             101          4
Sep-17             102          2
Aug-17             100          4              500
Aug-17             101          3              330
Aug-17             102          3              350
Jun-17              100          2              600
Jun-17              101          5              240
Jun-17              102          3              550

I have Top query that shows the best value achieved for one product (eg TVS)
SELECT prevTop.*
from
(Select Top 1 s1.SaleMonth,
p1.ProdDesc,
a1.AreaCode,
a1.AreaDesc,
s1.Value
FROM (Sales AS s1
INNER JOIN Product as p1 ON p1.ProdCode = s1.Prod_fk)
INNER JOIN Area AS a1 ON a1.AreaCode = s1.Area_fk
where p1.ProdCode = 100
ORDER BY Value DESC)
as prevTop

**SaleMonth      ProdDesc  AreaCode    AreaDesc          Value**
Jun-17             TVS             2               GERMANY        600

I need a query which outputs just the latest Sale Month (Sep-2017) and appends columns (Top Area, TopValue) showing the Top 1 value achieved for each of the three products from the previous months.

SaleMonth Prod_fk Area_fk TopArea TopValue
Sep-17 TVS 3 2 600
Sep-17 COMPUTERS 4 3 330
Sep-17 MOBILES 2 3 550

I think inner join would work but how to modify the prevTop Select to not use fixed condition p1.prodCode=100?


#2

Are the only one row in sales per salemonth, products, area?

If so, this might be what you're looking for

Query
select s.salemonth
      ,p.proddesc
      ,s.area_fk
      ,t.area_fk as toparea
      ,t.[Value] as topvalue
  from sales as s
       inner join product as p
               on p.prodcode=s.prod_fk
       inner join (select top(1) with ties
                          prod_fk
                         ,area_fk
                         ,[value]
                     from sales
                    order by row_number() over(partition by prod_fk
                                                   order by [value] desc
                                              )
                  ) as t
               on t.prod_fk=s.prod_fk
 where s.salemonth='Sep-17'
;

#3

Hi bitsmed,
Thanks for quick response.
There is only one row per month, area and product.
I forgot to mention my query needs to work with MS Acess 2013


#4

Further update using modified SQL for Access

select s.salemonth
      ,p.proddesc
      ,s.area_fk
      ,t.area_fk as toparea
      ,t.[Value] as topvalue
  from (sales as s
       inner join product as p
               on p.prodcode=s.prod_fk)
       inner join (select top 1
                          prod_fk
                         ,area_fk
                         ,[value]
                     from sales
                    order by [value] desc
                   )
                   as t
               on t.prod_fk=s.prod_fk
where format(s.salemonth, "mmm-yy")="Sep-17"
;

This only returns one row for
salemonth proddesc area_fk toparea topvalue
Sep-17TVS 5 2 600


#5

when actually need 3 rows from Sep-17 returned
SaleMonth Prod_fk Area_fk Value TopArea TopValue
Sep-17 TVS 5 2 600
Sep-17 COMPUTERS 2 3 330
Sep-17 MOBILES 3 3 550


#6

I think this might work for you

Query
select s.salemonth
      ,p.proddesc
      ,s.area_fk
      ,t.area_fk as toparea
      ,t.[Value] as topvalue
  from sales as s
       inner join product as p
               on p.prodcode=s.prod_fk
       inner join (select t2.prod_fk
                         ,max(t2.area_fk) as area_fk
                         ,t2.[value]
                     from (select prod_fk
                                 ,max([value]) as [value]
                             from sales
                            group by prod_fk
                          ) as t1
                          inner join sales as t2
                                  on t2.prod_fk=t1.prod_fk
                                 and t2.[value]=t1.[value]
                    group by t2.prod_fk
                            ,t2.[value]
                  ) as t
               on t.prod_fk=s.prod_fk
 where s.salemonth='Sep-17'
;

#7

When trying the above select, Access throws circular reference for 'value'

To get round the circular reference error I removed the Max function but that doesn't restrict the rows returned to 1 per product

select s.salemonth
      ,p.proddesc
      ,s.area_fk
      ,t.area_fk as toparea
      ,t.[Value] as topvalue
  from (sales as s
       inner join product as p
               on p.prodcode=s.prod_fk)
       inner join (select t2.prod_fk
                         ,max(t2.area_fk) as area_fk
                         ,t2.[value]
                     from (select prod_fk,
                             [value]
                             from sales
                            group by prod_fk, [value]
                          ) as t1
                          inner join sales as t2
                                  on t2.prod_fk=t1.prod_fk
                                 and t2.[value]=t1.[value]
                    group by t2.prod_fk
                            ,t2.[value]
                  ) as t
               on t.prod_fk=s.prod_fk
WHERE format(s.salemonth, "mmm-yy")="Sep-17";

> salemonth	proddesc	area_fk	toparea	topvalue
> Sep-17	TVS	        5	4	500
> Sep-17	TVS	        5	2	600
> Sep-17	COMPUTERS	2	5	240
> Sep-17	COMPUTERS	2	3	330
> Sep-17	MOBILES	        3	3	350
> Sep-17	MOBILES	        3	3	550

So question is how to make the MAX function work without giving me a circular reference in Access or another approach.


#8

Googling this error, I found that Access doesn't like it, when we alias to an already existing field name. Try this:

Query
select s.salemonth
      ,p.proddesc
      ,s.area_fk
      ,t.area_fk as toparea
      ,t.[Value] as topvalue
  from sales as s
       inner join product as p
               on p.prodcode=s.prod_fk
       inner join (select t2.prod_fk
                         ,max(t2.area_fk) as area_fk
                         ,t2.[value]
                     from (select prod_fk
                                 ,max([value]) as [maxvalue]
                             from sales
                            group by prod_fk
                          ) as t1
                          inner join sales as t2
                                  on t2.prod_fk=t1.prod_fk
                                 and t2.[value]=t1.[maxvalue]
                    group by t2.prod_fk
                            ,t2.[value]
                  ) as t
               on t.prod_fk=s.prod_fk
 where s.salemonth='Sep-17'
;

I'm sorry about this, but I'm not used to work with Access.


#9

:smiley: - Fantastic that works! Just some minor mods with bracketing for Access syntax

select s.salemonth
      ,p.proddesc
      ,s.area_fk
      ,t.area_fk as toparea
      ,t.[Value] as topvalue
  from (sales as s
       inner join product as p
               on p.prodcode=s.prod_fk)
       inner join (select t2.prod_fk
                         ,max(t2.area_fk) as area_fk
                         ,t2.[value]
                     from (select prod_fk
                                 ,max([value]) as [maxvalue]
                             from sales
                            group by prod_fk
                          ) as t1
                          inner join sales as t2
                                  on t2.prod_fk=t1.prod_fk
                                 and t2.[value]=t1.[maxvalue]
                    group by t2.prod_fk
                            ,t2.[value]
                  ) as t
               on t.prod_fk=s.prod_fk
WHERE format(s.salemonth, "mmm-yy")="Sep-17";

Result_success

Thanks bitsmed for your persistence to get to the solution.