0

In my database I have couple of stores and each store has couple of coupons sold and I need to find the rank as per the maximum coupons sold per store. I am using the following SQL code

set @rownum := 0;
SELECT @rownum := @rownum + 1 AS rank, sum(couponscount) as count, restaurant from coupons group by restaurant order by count desc

But, the output doesn't print the rank as per the maximum sum(couponscount) but its printing rank as per the store name alphabetical order.

How can I make it working?

I also need to retrieve the rank of a particular record and I am using the following SQL query for that

set @rownum := 0;
select rank from (SELECT @rownum := @rownum + 1 AS rank, sum(couponscount) as count, restaurant from coupons group by restaurant order by count desc) `storerank` where restaurant='some store name'

So, I need a first SQL query should be compatible with this one as well.

Thanks.

Karthik Malla
  • 5,570
  • 12
  • 46
  • 89
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Sep 17 '14 at 15:19

2 Answers2

1

This works in mysql. The temporary table is removed at the end of the session and unique to the session so there are no issues with two different people running the same process colliding. Add the "Drop" if you need to run this more than once in your session (and be sure that you use a name not in your current database)

    create temporary table temp select userid, sum(casesin) as CS from cases where csdate between '2014-07-25' and '2014-07-31' group by userid order by 2 ;

    select  @x:=@x+1 rrn, userid, CS from temp, (SELECT @x:= 0) AS s order by CS;
    Drop table temp;
4thregionref
  • 42
  • 2
0

This works on two files I have joined on ticket number to determine who sold the most cases on a ticket each day.

    SELECT IDENTITY(int, 1,1) AS Ranking, sum(qtydelivered) as QtyDelivered, orderticketnumber into #Temp from orderlines join orders on orderticketnumber = ticketnumber WHERE shippeddate = '2014-09-15' group by orderticketnumber order by 1 desc;  

    select * from #Temp;  

    drop table #Temp;
4thregionref
  • 42
  • 2