Solved

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

Posted on 2007-11-26
19
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20352731
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
ID: 20355916
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
ID: 20356064
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:fedders_world
ID: 20359807
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
ID: 20360943
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
ID: 20361565
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
ID: 20362002
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
ID: 20371786
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
ID: 20372131
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
 

Author Comment

by:fedders_world
ID: 20372415
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
ID: 20372886
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
ID: 20375156
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
ID: 20377055
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
ID: 20377350
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
ID: 20377401
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
ID: 20377514
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
ID: 20377646
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
ID: 20377721
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
ID: 31410982
Thank you so much ! You really helped me out :)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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