Need a bit of help for SQL return logic

I'm not the best with SQL ive managed to get it to to return info from the LogBlock sql for players for join date, and time played using:

Code:

//Get Play Time===
public String LBtime(String playera)
    {
        ResultSet rs = this.mysql.query("SELECT * FROM `lb-players` WHERE playername = '" + playera + "'");
        try
        {
            rs.first();
            String LBtime = rs.getString("onlinetime");
            return LBtime;
        }
        catch (SQLException e) {
        }return "Error";
    }
    //Get Join Date===
    public Date LBJoin (String playera)
    {
        ResultSet rs = this.mysql.query("SELECT * FROM `lb-players` WHERE playername = '" + playera + "'");
        try
        {
            rs.first();
            Date LBtime = rs.getDate("firstlogin");
            return LBtime;
        }
        catch (SQLException e) {
        }
        return new Date();
    }

But would anyone be willing to help me figure out how to return the top of each of these? this is where I'm stumped.

What I'm attempting to do is with the above when someone types /age (player)
It returns when they joined, and how many hours they played..I can get all that working. and the math done for calculating it so it tells "So and so is 140 days old, with 345 Hours ingame."

My roadblock in I wish to also add a /age top
And how to get it to queue the SQL and return the Top 5 players for oldest join date, and longest playtime.

Anyone willing to help me tackle this?

 

You can do Max(field) instead of *
or at the end, do "ORDER BY field" and the top row should be the oldest.

 

Hmm, that's where I'm hitting the wall.
my SQL-fu is next to none (well is none actually)

and how to write the code to pull it and loop 4 more times, pulling the next row each time is a mystery to me.

 

Here is a sample query you can use to get the base result set you need for the top aged player and playtimes;

Code:

SELECT playername,
MAX(calculation.for.age) firstlogin,
MAX(calculation.for.playtime) onlinetime
FROM lb-player 
GROUP BY 1

Now, there is a catch. Since a player could have first signed in a year ago and only amass 5 minutes of online time and someone else first signed in last week could amass 50 hrs of online time; you will have to have two commands to show age and played.

/age top
/age top played

would be the two commands I'd use and the depending on the argument you can append either:

Code:

ORDER BY 1 DESC LIMIT 4
/* OR */
ORDER BY 2 DESC LIMIT 4

depending on what the user has typed for arguments (top aged or top played).

 

ahh thanks!

yep just thought of that also, because of the case your described.

But I can do the separate commands, or have a command that runs both queues (I think) and then do the math to figure out the average hours per day (days old/totalplaytime), then just return the top "hours per day average" players.

Thanks, going to see if I can cobble this together now!

but where would the 'ORDER BY 2 DESC LIMIT 4' append to?

would it be:
"SELECT * FROM `lb-players` ORDER BY 2 DESC LIMIT 4"

please forgive my ignorance in the SQL command, Ive had a whole 2 days of learning SQL, what I did manage above I trialed and error-ed out to get what I needed

 

You would append that to the end of the query.. such as:

Code:

String sql = "SELECT ... ";
sql = sql + "ORDER BY 1 DESC LIMIT 4";

Or you can have separate methods to return the two different result sets from the user's command..

Code:

public int getPlayed(String playerName){
    String sql = "SELECT ... ";
    sql = sql + "ORDER BY 1 DESC LIMIT 4";
 
    return x //your result
}
 
public int getAge(String playerName){
    String sql = "SELECT ... ";
    sql = sql + "ORDER BY 2 DESC LIMIT 4";
 
    return x //your result
}

 

Got ya!
now time to see if I can do this without getting tons of console errors
thanks again for the explanation.