Solved

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

Posted on 2007-11-26
19
368 Views
Last Modified: 2010-04-21
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
Comment
Question by:fedders_world
  • 10
  • 9
19 Comments
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:fedders_world
Comment Utility
this is embarracing... but is there a place to upload cvs files on ee ... or should i put it eles where?

Aaron
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
browse to http://www.ee-stuff.com/
it should allow you to log in using your ee login, and upload files
0
 

Author Comment

by:fedders_world
Comment Utility
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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Closing Comment

by:fedders_world
Comment Utility
Thank you so much ! You really helped me out :)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now