CODEWITHSUNDEEP
mysqlgroup-bysql
user502014
user502014

Reputation: 2331

mysql get row number of a specific result

My website allows users to record bids. Each bid is saved individually and associated to a user Id. A user can have many bids which are used to add up to one overall bid which is displayed upon the site.

What I am trying to do in sql is return the position a users overall bid is from a result set.

The sql I am using is below but problems arise when I use the group by command - the ordering seems to revert back to the default db order rather than by the sum of a users bid amounts:

SET @rowcount = 0;
SELECT rowCount, userId FROM (
    SELECT userId, @rowcount := @rowcount + 1 as rowCount, sum(amount) as amount FROM bids group by userId order by amount desc
 ) t when product = xxxxx

appreciate if anyone knows if this is possible?

Upvotes: 0

Views: 909

Answers (3)

piotrm
piotrm

Reputation: 12356

You need to move rowcount incrementation out of subquery. And put your WHERE condition inside, otherwise your subquery will sum bids on all products for a given user.

SET @rowcount = 0;
SELECT @rowCount:=@rowcount+1 as rowcount, userId, amount FROM 
 (
    SELECT userId, sum(amount) as amount 
    FROM bids 
    WHERE product = xxxxx 
    GROUP BY userId 
    ORDER BY amount DESC
 ) t

Upvotes: 1

cs0lar
cs0lar

Reputation: 367

if I understand it correctly you could try something like this:

SELECT @rownum:=@rownum+1 ‘rank’, userId FROM (SELECT product, userId, sum(amount) AS amount FROM bids GROUP BY userId) t, (SELECT @rownum:=0) r WHERE t.product = xxxx ORDER BY t.amount DESC;

Upvotes: 0

rainerhahnekamp
rainerhahnekamp

Reputation: 1136

Have you already tried?

SET @rowcount = 0;
SELECT rowCount, userId FROM (
    SELECT userId, @rowcount := @rowcount + 1 as rowCount from 
     (select sum(amount) as amount, userId FROM bids group by userId) s
     order by s.amount desc
 ) t where product = xxxxx

Upvotes: 0

Related Questions