Top 5 Query MySql with Caveat

I'm having a hard time worth my query...I know how to get the top 5 sales but I'm having issues trying to get the previous year sales included and sorted in the query. I want to sort the stores by the 2010 totals and only show 2009 as a reference and it should have no bearing on the sort.

Example. Table: Store, Amount, Year
Store 10 1000.00 2010
Store 10 500.00 2009
-----------------------------------
Store 46 997.00 2010
Store 46 1500.00 2009
-----------------------------------
Store 5 850.00 2010
Store 5 430.00 2009
-----------------------------------
Store 99 777.00 2010
Store 99 401.00 2009
-----------------------------------
Store 101 550.00 2010
Store 101 700.00 2009
-----------------------------------
badtenantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Göran AnderssonCommented:
Get the top five stores in an inner query, then get the totals for separate years:
select Store, Amount, Year
from Table t
inner join (
  select top 5 Store, Amount
  from Table
  order by Amount desc
) x on x.Store = t.Store
where t.Year between 2009 and 2010
order by x.Amount desc, t.Year desc

Open in new window

badtenantAuthor Commented:
GreenGhost...Thanks for the quick response.  However, I still have a problem with the sort.

I would love to be able to do this via SQL rather than using an array...

Here is my SQL...

SELECT A.CUSTOMER_KEY,
         IFNULL(SUM(AGG_DAILY_SALES.TOTAL_SALES), 0) AS SALES,
         A.YEAR
    FROM (  SELECT A.YEAR,
                   B.CUSTOMER_KEY,
                   B.NAME,
                   B.SURNAME
              FROM (SELECT DISTINCT YEAR(TRANSACTION_DATE_KEY) YEAR
                      FROM AGG_DAILY_SALES
                     WHERE YEAR(TRANSACTION_DATE_KEY) >= YEAR(CURDATE()) - 1) A,
                   (SELECT DISTINCT (CUSTOMER_KEY), NAME, SURNAME
                      FROM DIM_LD_CUSTOMER) B
          ORDER BY B.CUSTOMER_KEY, A.YEAR) A
         LEFT OUTER JOIN AGG_DAILY_SALES
            ON A.CUSTOMER_KEY = AGG_DAILY_SALES.CUSTOMER_KEY
               AND A.YEAR = YEAR(AGG_DAILY_SALES.TRANSACTION_DATE_KEY)
         INNER JOIN (  SELECT CUSTOMER_KEY, SUM(TOTAL_SALES) AS SALES
                         FROM AGG_DAILY_SALES
                        WHERE YEAR(TRANSACTION_DATE_KEY) >= YEAR(CURDATE())
                     GROUP BY CUSTOMER_KEY
                     ORDER BY SALES DESC
                        LIMIT 5) C
            ON A.CUSTOMER_KEY = C.CUSTOMER_KEY
GROUP BY A.CUSTOMER_KEY,  YEAR DESC

The OUTER join returns a record even if the customer didn't have sales...

Here is the output:

591   738.19    2010
591   0               2009
737   666.87   2010
737   0              2009
1157   714.13   2010
1157   0             2009
1541   691.51   2010
1541   0              2009
1701   995.82   2010
1701   0              2009

If I sort my sales again it messes up the output.
badtenantAuthor Commented:
Found a solution...I added rank to the inner join and used that to sort.  Now I just need to clean up this ugly SQL.

    SELECT A.CUSTOMER_KEY,
         IFNULL(SUM(AGG_DAILY_SALES.TOTAL_SALES), 0) AS SALES,
         A.YEAR
    FROM (  SELECT A.YEAR,
                   B.CUSTOMER_KEY,
                   B.NAME,
                   B.SURNAME
              FROM (SELECT DISTINCT YEAR(TRANSACTION_DATE_KEY) YEAR
                      FROM AGG_DAILY_SALES
                     WHERE YEAR(TRANSACTION_DATE_KEY) >= YEAR(CURDATE()) - 1) A,
                   (SELECT DISTINCT (CUSTOMER_KEY), NAME, SURNAME
                      FROM DIM_LD_CUSTOMER) B
          ORDER BY B.CUSTOMER_KEY, A.YEAR) A
         LEFT OUTER JOIN AGG_DAILY_SALES
            ON A.CUSTOMER_KEY = AGG_DAILY_SALES.CUSTOMER_KEY
               AND A.YEAR = YEAR(AGG_DAILY_SALES.TRANSACTION_DATE_KEY)
         INNER JOIN (  SELECT A.CUSTOMER_KEY,
                               A.SALES,
                               @prev := @curr,
                               @curr := A.SALES,
                               @rank := IF(@prev = @curr, @rank, @rank + 1)
                                  AS rank
                          FROM (  SELECT CUSTOMER_KEY, SUM(TOTAL_SALES) AS SALES
                                    FROM AGG_DAILY_SALES
                                   WHERE YEAR(TRANSACTION_DATE_KEY) >=
                                            YEAR(CURDATE())
                                GROUP BY CUSTOMER_KEY
                                ORDER BY SALES DESC
                                   LIMIT 5) A,
                               (  SELECT @curr := NULL, @prev := NULL, @rank := 0)
                               sel1) C
            ON A.CUSTOMER_KEY = C.CUSTOMER_KEY
GROUP BY A.CUSTOMER_KEY, YEAR DESC
ORDER BY RANK, YEAR DESC

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.