CODEWITHSUNDEEP
mysqlsql
Amar Mond
Amar Mond

Reputation: 195

Row Count per Group in mySQL Query

I have the following query:

    SELECT ebq1.`idBUSINESS`, ebq1.`KEYWORD_TEXT`, ebq1.`CITY`, ebq1.`BID_AMOUNT`
    FROM `elevated_business_queue` ebq1
    GROUP BY ebq1.`KEYWORD_TEXT`, ebq1.`CITY`, ebq1.`BID_AMOUNT`
    ORDER BY ebq1.`KEYWORD_TEXT`, ebq1.`CITY`, ebq1.`BID_AMOUNT` DESC

I want it to return top 10 bids for every keyword / city group (for every ebq1.KEYWORD_TEXT, ebq1.CITY, ebq1.BID_AMOUNT group)

I tried putting a LIMIT 10 on the query but then it just returns 10 rows - I want a maximum of 10 rows returned per group. Is there a way to do this?

I also want the position of the bid returned for every keyword / city / bid. I tried using the following query:

SELECT `idBUSINESS`, `KEYWORD_TEXT`, `CITY`, `BID_AMOUNT`, @rn:=@rn+1 AS `POSITION`
FROM (
    SELECT ebq1.`idBUSINESS`, ebq1.`KEYWORD_TEXT`, ebq1.`CITY`, ebq1.`BID_AMOUNT`
    FROM `clearindia`.`elevated_business_queue` ebq1
    GROUP BY ebq1.`KEYWORD_TEXT`, ebq1.`CITY`, ebq1.`BID_AMOUNT`
    ORDER BY ebq1.`KEYWORD_TEXT`, ebq1.`CITY`, ebq1.`BID_AMOUNT` DESC LIMIT 10
) t1, (SELECT @rn:=0) t2;

but it gives me positions from 1 to 10 ignoring my group. I want the position returned for each group (top 10 bids in each city and keyword.).

This query returns the following:

# idBUSINESS, KEYWORD_TEXT, CITY, BID_AMOUNT, POSITION
'5', '1', 'New York', '2.50', '1'
'7', '1', 'New York', '2.30', '2'
'1', '1', 'New York', '1.50', '3'
'7', '2', 'New Jersey', '3.50', '4'
'5', '2', 'New Jersey', '2.50', '5'

I want it to return:

# idBUSINESS, KEYWORD_TEXT, CITY, BID_AMOUNT, POSITION
'5', '1', 'New York', '2.50', '1'
'7', '1', 'New York', '2.30', '2'
'1', '1', 'New York', '1.50', '3'
'7', '2', 'New Jersey', '3.50', '1'
'5', '2', 'New Jersey', '2.50', '2'

Upvotes: 0

Views: 130

Answers (1)

Vikas Hardia
Vikas Hardia

Reputation: 2695

Try this 

 Select * from (
  SELECT  @row_num :=       IF(@prev_value=o.CITY,@row_num+1,1) AS RowNumber
   ,o.idBUSINESS
   ,o.KEYWORD_TEXT
   ,o.CITY
 , o.BID_AMOUNT
   ,@prev_value := o.CITY
    FROM  elevated_business_queue o,
  (SELECT @row_num := 1) x,
  (SELECT @prev_value := '') y
     ORDER BY o.CITY, o.BID_AMOUNT DESC) as temp_tab
  Where 
  RowNumber <11

Upvotes: 1

Related Questions