• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Ranking / Sorting rows in SQL by votes and catagories ASP.NET

Hey guys,
Im using MySQL with ASP.NET/VB

I have a Table which hold an PictureId as well as a votes coloum and a catagorie id.
The votes col stores then number of votes a picture has (its updated everytime someone votes)
For example:
Pictureid        Votes        CatId
1                     200           1
2                     212           1
3                     100           1
4                     110           2
5                     2991         2
6                     321          3

What i want to be able to do is rank each row according to votes and catagorie in another table.
For example
Table: Ranks_Table
Pictureid        Rank        CatId
1                     2           1
2                     1           1
3                     3           1
4                     2           2
5                     1           2
6                     1           3


I have been using:
"REPLACE INTO ranksTable(pictureID, rank) SELECT yT.PicId, @r:=@r+1 as rank FROM (SELECT @r:=0) r, picture_count yt ORDER BY yt.VoteCount DESC"

to rank pictures based soley on votes and NOT catagories.

If you could help it would really help!

Thanks in advance

Feds
0
fedders_world
Asked:
fedders_world
  • 10
  • 9
1 Solution
 
imitchieCommented:
this should do you nicely, courtesy of MySQL

http://forums.mysql.com/read.php?32,65494,89649#msg-89649
REPLACE INTO ranksTable(pictureID, CatID, rank)
SELECT yT.PicId, CatID,
 case when @C=CatID then @r:=@r+1 else @r:=1 end rank,
 case when @C<>CatID then @C=CatID end CatID
FROM (SELECT @r:=0) r, picture_count yt
ORDER BY CatID, yt.VoteCount DESC

Open in new window

0
 
fedders_worldAuthor Commented:
Hi imitchie,

I tired your code... i altered it a bit to match my tables and cols exactly:

REPLACE INTO poserrraward_rank_table(picid, CatId, Rank)
SELECT yT.PicId, CatId,
 case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
 case when @C<>CatId then @C=CatId end CatId
FROM (SELECT @r:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC

However im getting the error:
Column count doesn't match value count at row 1

Thanks for your help in advance

Aaron
0
 
imitchieCommented:
hi aaron, please use this
REPLACE INTO poserrraward_rank_table(picid, CatId, Rank)
SELECT yT.PicId, case when @C<>CatId then @C=CatId end CatId,
 case when @C=CatId then @r:=@r+1 else @r:=1 end Rank
FROM (SELECT @r:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
fedders_worldAuthor Commented:
Hi imitchie,

I just tired that and it did not work exactly as i need it.
It gave every row a rank of 1 and left CatId blank in  poserrraward_rank_table

Aaron
0
 
imitchieCommented:
I thought @c would persist, but I've now added the else clause. this should work
REPLACE INTO poserrraward_rank_table(picid, CatId, Rank)
SELECT
  yT.PicId,
  case when @C<>CatId then @C:=CatId else @C end CatId,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank
FROM (SELECT @r:=0, @c:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC

Open in new window

0
 
fedders_worldAuthor Commented:
We are getting closer, thanks for helping.

However there is still an issue.
It now correctly sets out the Cat id and picid etc. However the ranking is wrong. Rather then giving me the rank of the row for that catagory it is giving an overall rank of every row.

For example
Picid    CatId  Rank
1         1         1
2         1         3
3         2          4
4         1          5
5         2          2

It should however be doing this

Picid    CatId  Rank
1         1         1
2         1         2
3         2          1
4         1          3
5         2          1

(Assuming picid 1 has more votes thant picid 2 etc)

Thanks
Aaron

0
 
imitchieCommented:
okay try this
REPLACE INTO poserrraward_rank_table(picid, Rank, CatId)
SELECT
  yT.PicId,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId
FROM (SELECT @r:=0, @c:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC

Open in new window

0
 
fedders_worldAuthor Commented:
So i think we are getting closer.
But now it is giving incorrect ranks.

For example it is givint all item with the Picid 3 with the rank 1.

Thanks Aaron.
0
 
imitchieCommented:
I got the right results based on your result set.
You _cannot_, must not change the order by as it affects the := processing.
can you please recheck?

REPLACE INTO poserrraward_rank_table(picid, Rank, CatId)
SELECT
  yT.PicId,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId
FROM (SELECT @r:=0, @c:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC
0
 
fedders_worldAuthor Commented:
Hey again.

Just so there is no confusion (im probley making a mistake)

This is a pic of picutre_count in MySql
http://www.flickr.com/photo_zoom.gne?id=2072786045&size=o
and this is a picture of poserrraward_rank_table after i have executed the query.
http://www.flickr.com/photo_zoom.gne?id=2072786005&size=o

I copy and pasted your query and thats what im getting.

Aaron
0
 
imitchieCommented:
if you have executed the earlier queries a few times, you may have to clear the table first

DELETE FROM poserrraward_rank_table;

REPLACE INTO poserrraward_rank_table(picid, Rank, CatId)
SELECT
  yT.PicId,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId
FROM (SELECT @r:=0, @c:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC;

or simply check this

SELECT
  yT.PicId,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId
FROM (SELECT @r:=0, @c:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC;
0
 
fedders_worldAuthor Commented:
I'm afraid im still getting the wrong ranking i tired both of whst you suggested.

What do you think it could be?

Aaron
0
 
imitchieCommented:
how big is your data? any chance of uploading a mysqldump/csv of the one table to ee-stuff? I would really like to help you with this, but at the moment, it seems to work for me but not for you!
0
 
fedders_worldAuthor Commented:
this is embarracing... but is there a place to upload cvs files on ee ... or should i put it eles where?

Aaron
0
 
imitchieCommented:
browse to http://www.ee-stuff.com/
it should allow you to log in using your ee login, and upload files
0
 
fedders_worldAuthor Commented:
For some reason ee stuff tells me my questions invalid.
Not to worry i have uploaded it to a server
This is the cvs of the table picture_count

http://www.sissitgroup.com/EE/picture_count.csv

and this is the cvs of the result when i try the query
http://www.sissitgroup.com/EE/select_statment.csv

SELECT
  yT.PicId,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId
FROM (SELECT @r:=0, @c:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC;

Thanks

Aaron :)
0
 
imitchieCommented:
Good on you!
try either of these:

-- ranks allow multiple on the same, i.e. 1,1,1,2,2,3,4,5,5,6

SELECT
  PicID,
  case when @C=CatId then
    case when @i=VoteCount then @r:=@r+1  else @r end
    else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId,
  @i:=VoteCount VoteCount
FROM
(
SELECT
  yT.PicId, CatID, VoteCount
FROM (SELECT @r:=0, @c:=-1, @i:=0) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC
) X

-- ranks keep going up, i.e. if two had the same vote, one gets 5 one gets 6

SELECT
  PicID,
  case when @C=CatId then @r:=@r+1 else @r:=1 end Rank,
  case when @C<>CatId then @C:=CatId else @C end CatId
FROM
(
SELECT
  yT.PicId, CatID, VoteCount
FROM (SELECT @r:=0, @c:=-1) r, picture_count yt
ORDER BY CatId, yt.VoteCount DESC
) X

0
 
imitchieCommented:
Now that I have your data, this is even better

-- equal placed ranks skip the next place, i.e. 1,2,3,3,5,6,6,6,9...

REPLACE INTO ranksTable(pictureID, rank)
select x.picID, x.Rank
from (select a.picid, a.catid,
(select 1 + count(*) from picture_count b
where b.catid = a.catid
  and b.votecount > a.votecount) rank
from picture_count a) x
0
 
fedders_worldAuthor Commented:
Thank you so much ! You really helped me out :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now