in reply to Re: Ranking MySQL Entries?
in thread Ranking MySQL Entries?

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'

[download]

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?

I love it when a program comes together - jdhannibal

Replies are listed 'Best First'.
Re^3: Ranking MySQL Entries?
by Corion (Patriarch) on Nov 20, 2013 at 18:09 UTC

    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?

[reply]
      I'm not sure...it's weird. I use phpmyadmin, and run that exact SQL statement against the database and get exactly what I want, but when I put it in a query for DBI to do, it says I have an error in my SQL syntax? Shouldn't whatever works on phpmyadmin just be able to be copy and pasted into the query for DBI->do?
      I love it when a program comes together - jdhannibal
[reply]

        DBI->do does not return values.

        In principle still, you should be able to "just" use the same SQL.

        As you don't show the relevant code, it's hard to advise you further. Showing the relevant code and data, and making it as self-contained as possible helps us help you better.

[reply]
[d/l]
Re^3: Ranking MySQL Entries?
by kcott (Archbishop) on Nov 21, 2013 at 00:31 UTC

    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;

    [download]

    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;

    [download]

    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 },
]

    [download]

    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!

    -- Ken

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom