Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Ranking MySQL Entries?

by jdlev (Scribe)
on Nov 20, 2013 at 06:40 UTC ( #1063456=perlquestion: print w/ replies, xml ) Need Help??
jdlev has asked for the wisdom of the Perl Monks concerning the following question:

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?

I love it when a program comes together - jdhannibal

Comment on Ranking MySQL Entries?
Re: Ranking MySQL Entries?
by boftx (Chaplain) on Nov 20, 2013 at 06:56 UTC

    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?

    It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.
Re: Ranking MySQL Entries?
by barrd (Parson) on Nov 20, 2013 at 07:02 UTC
    Hi jdlev,
    You may wish to consult the fine MySQL documentation.
Re: Ranking MySQL Entries?
by kcott (Abbot) on Nov 20, 2013 at 07:04 UTC

    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.

    -- Ken

      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?

      I love it when a program comes together - jdhannibal

        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?

        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!

        -- Ken

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1063456]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (9)
As of 2014-09-17 11:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (73 votes), past polls