Ranking MySQL Entries?

[jdlev]

I literally don't have the slightest idea where to begin on this one. Let's say you have a column called "score". I don't want to order, I want to return rank. From the top score to the bottom score. Any ideas on where to start?

[kcott]

G'day jdlev,

"I literally don't have the slightest idea where to begin on this one."

Given the lack of information, nor do I, really.

Have you written code to get data from the database? What does this data look like? What data structure have you used?

How does "rank" differ from "order" in this context? If you hadn't written "I don't want to order", I would have suggested sort. Perhaps that is what you want.

Have a look at the guidelines in "How do I post a question effectively?". This should provide you with a better idea of what you need to post in order to help us to help you.

[jdlev]

Hi Ken :)

The following SQL statement works, but I don't know how to implement it using perl so it returns their rank.

SELECT MAX( x.rank ) AS rank
FROM (

SELECT id, cityAb, offPPG, @rownum := @rownum +1 AS rank
FROM team_stats t
JOIN (

SELECT @rownum :=0
)r
ORDER BY t.offPPG DESC
)x
WHERE x.cityAb =  'Jax'

If I had some perl code that would order a set of records by a specific column, and then return the row number of the record that matches the query (in the above example, whichever city equals 'Jax'), then I could get it to work. So the above SQL statement works perfectly, I just don't know how to implement it through perl. I'm using DBI.

Hope that helps?

[Corion]

DBI has various examples of how to return data from an SQL SELECT statement.

It seems to me that you already have a SELECT statement that provides the information you need.

Where exactly lies your problem? What Perl code have you written? How does it fail?

[kcott]

You've been pointed to documentation which you appear not to have read. You been asked for information which you haven't supplied. Without doing these things, I don't know how you think we can help you.

I provided a link to sort. Here you'll find many examples including this basic one for sorting numerical data in descending order:

    # sort numerically descending
    @articles = sort {$b <=> $a} @files;

Corion provided a link to DBI (in Re^3: Ranking MySQL Entries?). If you're unfamiliar with that module, look the "Simple Examples" section in that documentation.

Once you have your data, in whatever data structure you choose, you'll probably need a more complicated sort than the basic example shown above. Perhaps something like this:

#!/usr/bin/env perl -l

use strict;
use warnings;
use Data::Dump;

my @unordered = (
    { id => 12, rownum => 1, rank => 27 },
    { id => 31, rownum => 3, rank => 72 },
    { id => 45, rownum => 5, rank => 54 },
);

my @ordered = sort { $b->{rank} <=> $a->{rank} } @unordered;

print 'Unordered:';
dd \@unordered;
print 'Ordered:';
dd \@ordered;

Output:

Unordered:
[
  { id => 12, rank => 27, rownum => 1 },
  { id => 31, rank => 72, rownum => 3 },
  { id => 45, rank => 54, rownum => 5 },
]

Ordered:
[
  { id => 31, rank => 72, rownum => 3 },
  { id => 45, rank => 54, rownum => 5 },
  { id => 12, rank => 27, rownum => 1 },
]

You need to follow the guidelines in "How do I post a question effectively?". Note what it says about posting code, data, output and warning/error messages. Pay particular attention to what it says about providing "a minimal script that reproduces your problem" in your post. Unless you do this, we can not help you further!

[boftx]

Can you give us an example of what you mean by "rank" vs. "order"? Do you have sample output that can show us actual output vs. expected output? Can you show us any code that produces the above?

[barrd]

Hi jdlev,
You may wish to consult the fine MySQL documentation.