MYSQL :: Ranking Results

Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking.

For instance:

Username | Rank
steve | 6000
bob | 5000
jane | 4000
chris | 3000
brian | 2000
tim | 1000
molly | 0

Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following:

bob | 5000
jane | 4000
chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this.
brian | 2000
tim | 1000

What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from.

For instance, lets say Chris is row 583 of my database... how do I retrieve that number?

Ranking Scores
I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table.
the link below shows an example image of what i need to happen.

A Ranking Leaderboard
I am trying to design and implement an online leaderboard in MySQL and PHP.  Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db.  It is the query you write/code which does the sorting.  How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc.  However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical.  You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking?  Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before.  I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Ranking MySQL
I have a query that extracts the last 7 records from a database based on its criteria and have it ordered by its date. What i want to do is add a column in the select statement and put in the numbers from 1 to 7 into the column in this added column.

I'm using the Space(1) as Number function but i am wondering how do I put 1 to 7 in the table. Its pretty much like a ranking system.

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated

Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts?
And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.

Ranking Leaderboard In MySQL!?
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.

I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.

Ranking For Multiple Columns
A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly.

So the result should be something like this:

ID     Amount1  Ranking1  Amount2 Ranking2
----   -------- --------- ------- --------
135     123.34      4       23.00     3
184     160.23      1       60.89     2
845     140.22      3      100.20     1
987     155.00      2        1.20     4

The ID isnt important here.

The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one.

Is there a way to get the ranking filled in  the same pass as the amounts are calculated?

Sorting And Put Ranking In Field
I have 3 field in my table Ranking, Name and Score. Is it possible to sort all record to order by Score and put Ranking no. to each record by using query not much?

Scoreboard Ranking System
I have created a high score table that will potentially store > 10000 user scores, and need to determine ranking for items pulled out. Ther have been posts detailing how to do this when selecting ALL records, but in my case, I need to pull out specific rows, and determine their rank against the entire table.
For instance, a user gets a high score that would rank them at #1200, I need to pull out their score, as well as the 3 above and 3 below. I accomplished this by storing the insert_id, and selecting rows where the score is above and below (in seperate queries), but this makes it quite impossible to get a rank.
An alternative method is having a rank column and updating it every time a new score is added, but if it cant be done in one query, it would be a very inefficient method.
Does anyone have any solutions on how to approach this? I am using mysql 3.23, so I dont have use of sub-queries.

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

Full-Text Search: Truncation Operator And Relevance Ranking?
We would like to begin supporting the truncation operator on our website's search engine--however, we still require the results to be sorted by relevance. If a client enters "televis*" into the search engine, and we perform the search in boolean mode, pages containing "television", "televise", "televised", etc will be returned. However, because it is a boolean mode search, all rows will have a relevancy value of 1.

A user-provided "workaround" on the MySQL site said to use the same keywords but NOT in boolean mode to get a usable relevance ranking. However, a standard fulltext search for "televis" or "televis*" will return no rows.

Add Up Results
Not sure if I should ask this in PHP forum or this one?
I think this is a simple request. I have a table with names and dollar amounts of pledges. What I need to do is grab all the amounts for the same person and then add it up to a total amount that they owe.
ie: Table is like this.
Don $500
Joe $100
Don $1250
Don $1250
Fred $300
I need to pull all the info that are for Don and then add up the amounts, for example this one Don owes a total of $3000.

Getting Results
i use mysql 4.0.22 which means i cant use subquery's right?
Ive tried using them but it dident go well.
This is what im trying to accomplish

SELECT service FROM rate WHERE type = 'as'

service must be equal to se
SELECT se FROM autosurf where st = 0

Different Results
I have a nightmare of a problem, where in a Query I am attempting to calculate percentages. I had the query running on an earlier version of mysql and it worked fine, and it is now running on the latest version and doesn't return the correct value.

This is the query : Select DISTINCT std.student_f_name, std.student_m_name, std.student_l_name, lt.time_from, lt.time_to,
lt.day, TIME(rda.clockinTime),
cast(($variable1/$variable2)*100 as decimal(3,2))
from students std, record_attendance rda, lecture_times lt
where std.studentID = ?
AND rda.studentID = std.studentID
AND rda.lectureTime = lt.id
AND lt.id=?

This correctly returned 33.3 percent in the older version, however in the new version returns 9.9 %. Does anyone know how this can be fixed?

All Results
Anyone know how to Get all results not starting with a letter?

Unique Results
I am querying my mysql database from php like so:

$query = "
SELECT $wpdb->posts.*
FROM $wpdb->posts
LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
WHERE $wpdb->post2cat.category_id NOT IN (2, 6)
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";

Its joining a second table (post2cat) as the main table (posts) does not have a value (category_id) which I need to check is not 2 or 6. As a result if an item in posts has two category ids (and so is listed twice) the item goes into my mysql result twice which I don't need. Is there a way I can return only unique results (i.e. not twice)??

This is the Wordpress mysql structure if anyone is familiar with it.

Results Into One Cell
I need to query a table and have all the results show in one cell.

Example: Select last_name, age from costomer where last_name = "Franks";

If there are three members to the frank faimily, the result would be ...

Subquery With Many Results?
So i'm looking for a way to have a query with a subquery, but the subquery returns more than one item.

ex.
SELECT * FROM menu where parent_id = (SELECT menu_id FROM menu WHERE parent_id = 0)

the subquery will return [22,30,33]
I would like the top query to execute logically as follows:

SELECT * FROM menu where parent_id = 22 OR parent_id = 30 OR parent_id = 33

I can't predict the count of the subquery.

I can do this with loops in my php but I have a feeling that it is possible to do this with a single SQL statement (multi subqueries).

Specific Row On Top Of Results
Someone knows how I could list all the records of a table with the very first result being of a specified value that exists in the table?

For example :

column 1
a
b
c
d

Query result with c needed on top :

c
a
b
d

Query result with b needed on top :

b
a
c
d

Duplicate Results
I have a DB with a name column. Sometimes, the names might duplicated, so i was wondering how to write a query that says, show me 1 of the records instead of both...?

No Results When Just One Record
I'm using this query to retrieve news and comments:

$query = "SELECT SQL_CALC_FOUND_ROWS *,
DATE_FORMAT(n.date,'%d/%m/%y') AS date,
COUNT(c.news_id) AS comments,
n.news_id,
n.icon_id,
n.views,
n.name,
n.title,
n.news
FROM news AS n
LEFT JOIN comments AS c ON (n.news_id = c.news_id)
GROUP BY n.news_id
ORDER BY n.date DESC
LIMIT $offset, $limit";

The problem is that if both tables are empty and I insert one news, it won't show up until I insert another news or one comment. Why is that happening? Am I using the wrong Join?

Sorting My Results
This is the sql Im using in a recordset using dreamweaver:

SELECT racer_name,SUM(points)
FROM race_results
WHERE `class` = 'gas'
GROUP BY racer_name
ORDER BY race_results.points DESC

Im really new to this but the "SUM" is working fine, but im having a problem with
ORDER By function,...its order by "points" which in my DB is a samllint ---- It just
needs to sort by that number (points) by largest to smallest,

List Of Results
I have a table with data

User | Value
-------------
A | 1
A | 21
A | 33
B | 41
B | 56
C | 68

and I want to produce a query that will return as the result.

User | Values
--------------
A | 1,21,33
B | 41,56
C | 68

Is this possible? How so?

Offsets On Results
How do I produce only 10 results and specify an offset (where the results will begin)? I'm working on something like a search results page on PHP and I was wondering how to do this to reduce server load.

Not All Results Are Displayed
I have a page that by default will list a bunch of subjects. When the user clicks on a subject, that same page will reload and display a list of articles pertaining to that subject by passing the subject ID in the url. Same page, different info. However, I cannot get all of the results to display. Code:

Randomizing The Results.
(I am using PHP on a *nix box)

I would like to make the results returned from this query return in a random order. ie: 1 2 3 4 5 => 2 4 1 3 5 , and different each time the query is run.

CODEselect f.ID as fid, d.Title as denom, c.denom as did, c.ID as ID, c.name, c.imgw, c.bio, c.imgh, c.city, s.state as state, co.description as size FROM featured as f, churches as c, states as s, congregation as co, denomination as d where s.stateid = c.state AND co.ID = c.size AND c.ID = f.clientid AND f.type = 'c' AND d.ID = c.denom

Results Navigation
I have a results page and the results paging nav is pretty simple as it stands:

< | Previous 1 2 3 4 5 6 7 8 9 10 etc | Next >

The problem is that when there are alot of results there can be up to a few hundred of these page links.

So I need something like the nav that is used on coding forums and other sites:

< 1 2 3 4 5 > Last , First < 5 6 7 8 9 > Last

or similar...

Does anyone know where I can find a class/script etc to do this?

Retrieving Top 100 Results
I am trying to get an average value from my database but I only want to retrieve the average out of the top 100 results. The column I want to average contains values from 0.1 to 10.0.

So I want to get the average from the top 100 values in this column. The average syntax is clear but what do I have to add?

This is what I have now:

$query="select AVG(M_RATING) as rating FROM rate_members WHERE M_COUNTRY='SE'";
But this gives me an average based on all values in the column.

Grouping Results
I have an query that selects some records from a database what need to be grouped by an ID value. I can have multiple records with the same ID and they need to be grouped by it, an example:

an array from query would be:
1. 1 record one
2. 1 record two
3. 8 record one
4. 8 record two
5. 8 record three

and the final output that I need is

1
-------
record one
record two

8
--------
record one
record two
record three

How To Join Results ?
Is it possible to join results of the select query ?

So If I get data from two rows (firstname, secondname)Can I somehow join query results like this: firstname.secondname

Example:

QUERY: SELECT firstname,secondname FROM table;

RESULTS:
John.Doe
Mike.Monroe
Kate.Moss

Combining Results
I've a table as follows:

film_id | t_day | t_time
1 | 2006-06-10 | 12:30
1 | 2006-06-10 | 13:30
1 | 2006-06-11 | 11:45
1 | 2006-06-11 | 12:45

I want to select all the data where film_id = 1 and have the results in a form similar to this:

t_day | t_times
2006-06-10 | 12:30, 13:30
2006-06-11 | 11:45, 12:45

Is it at all possible through just a single MySQL query so that I can easily navigate the days and the times.

Block Results
If I wanted to list my results in blocks of 500 records how would I do this? I looked up cursors and found it confusing. I read up about limits but then it would only list the first 500 records and ignore the rest.

The main reason I'm asking is because my Java app is crashing because of too much data being sent to it from the MySql.

The way to control data is it through MySql or Java?

Grouping Results
I need to create a query that selects various rental properties from a table depending on certain criteria and to have them grouped according to their region.

The table I have (from another program that links to the dbase so I can't change it) has a field called regiunea where the area of town the property is in is recorded as a text entry, eg. centre or west or east etc.

Obviously I can query on a specific area in the query, but as we are looking at group bookings for multiple properties we may not have enough properties in one area to fulfill the clients needs.

So, I want to group the appartments by area so that if there are enough appartments free in say the north part of town it will display them and if not it will display a part of town that does have the correct number. The client does not have any say which area the appartments are in but they do get a choice of a different area too (they are given the price difference).

I have tried to use the mysql group by statement in my select but it doesn't seem to work as I envisaged.

Multiple Results From $row[#]
i have a script for an image gallery, but in the spots where all the images (in the database) are supposed to be displayed, i have $row[6] on all of them, which produces like 12 of the same image... how do i get $row[6] to have different results from newest to oldest and automated?

SQL Results As Variables
I hope someone here can help me figure this out. I was looking for a way to give each result from a SQL Query a variable. My reason for doing this is I'm trying to make each result an item in a drop down box to make searching the database easier for the users. Anyone have any ideas? I know how to sort & arrange the results, but how do you assign each one (no duplicates) a variable?

Results Of A SELECT SUM
I am trying to figure out how to use the results of a SELECT SUM using Java (which I know very little about)

rs = databaseStatement.executeQuery ("Select sum (unitcost) as total from subcomponents")

The query seems to run correctly

so, I now say
System.out.println (rs.getString("total"));
or
System.out.println (rs.getDouble("total"));

and the result is

java.SQL.SQLException: Before start of result set

What am I missing.

Ordering Results In Php
The code for displaying such holidays is:

<a href="availableproduct.php?region=fiji">

Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.

Can I Specify Order Of Results [was: Is This Possible?]
I am trying to query a table with real estate listings. I have variables company, price, bedrooms.

I want to do a search of all listings between x and y price, and between b and c amount of bedrooms. I can do all that stuff just fine. However, I want the query to return listings from MY company first. So basically a query there company=mine and then goes through the search, and then company=any and goes through again. Any idea how to do this or what its' called?

Displaying Results By Day
How can I display results by highest count for each day? For instance, top ten searches by day sorted by count().

Here is the code I've come up with so far, it manages to understand the current date and display some of the searches for that day, yet, does not display from highest to lowest, though the count() output works just fine.

$sql = "SELECT keyword, count(keyword) AS keywordcount,curdate() as date FROM queries GROUP BY keyword ORDER BY date DESC LIMIT 0, 10";

Here is what the db table looks like
id (auto_increment)
ip
keyword
time (datetime)

I know you might suggest using DATE_FORMAT(time,%Y,%m,%d) but i just could not figure out how to get that work correctly, maybe a BETWEEN? I think maybe the datetime format is messing it up and I need to separate days and compare.

Results Dated
I'm looking for a way to pull only the events that will happen in the next 7 days from my database. I wrote this:

SELECT date, classID FROM EVENTS WHERE classID != '0' AND date <= ( DATE_ADD( CURDATE( ) , INTERVAL 7 DAY ) ) ;

but it looks like this is just pulling all of the events from the table.

Limit Results
Is there any way to limit the results of a query to the number of rows found minus 1? I'm trying to effectively emulate a logical or in a union query. I have three union segments, and I want either the X number of rows that the first two return, or the one row that the last segment returns (or nothing). So, I was hoping to be able to basically do something like select <...> limit (if(NUM_ROWS > 1, NUM_ROWS - 1, NUM_ROWS)).

For more of the backstory, I'm setting up alias resolution in postfix. What I need is to return the recipients (unknown number of rows) of a mail group (first segment), the owner of an alias (second segment), *or* a catch-all address for the domain if nothing else is found. I may be able to rework my postfix setup somehow, but I figured I'd ask about this first.

Results W/ A LIMIT BY
I have a table where I need to pull out the 5 latest records only. There is an INT 10 field called category. I only want one record from each of the following items in category (1,3,4,5,7). I can't do a Distinct because the other fields are not distinct. If I do a LIMIT BY, I can't guarantee that I'll have one of each. How do I get out the data?Here is my not working query:

SELECT username, title, category, price
FROM mister
WHERE category IN(1,3,4,5,7)
ORDER BY laststamp DESC
LIMIT 5

Results Grouping
I need to group products (and display them differently) that have the same 'name' in a list of search results. this application is like a shopping comparison site.

basically, i have one db that is a massive list of products from different vendors. a keyword search will then pull results and display them. for any products that come up twice (with the same 'name' but from different vendors) in the results, i want that result to be displayed differently than the single resluts.

All Results Of A COUNT
The following query works as I need it to, except that 'kingdom's that don't have any associated 'territory.tile's do not get included in the results.
Code:

SELECT name, gold, COUNT( territory.tile ) AS land
FROM kingdom
LEFT JOIN structure ON structure.kingdom = kingdom.name
LEFT JOIN territory ON structure.id = territory.structureID
GROUP BY kingdom
ORDER BY gold DESC
LIMIT 0 , 20

What do I need to change so that all 'kingdom's are included in the results, whether they have associated 'territory' or not?

Derived Results
say theres a table with 4 columns

col1 col2 col3 col4
val1 val2 4     3
val3 val1 5     4
val1 val4 6     2

I am trying to find out how many times value one is in one column based on a condition, and how many times its in the other column based on another condition. Unfortunately I cant use subqueries to do it cause of the hosting provider.
The below just sums the total times val1 appears multiplied by 2. Not really sure what I am doing wrong.

select count( col1 * ( col3 > col4 )  )  + ( col2 * ( col4 > col3 )  )  AS count FROM table WHERE col3 =  'val1' OR col4 =  'val1'

Ranking Results

Im trying to display baseball team stats, along with the ranking for that particular league. for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20. I want to show on each individual team page their stat, with the rtanking for the league.

Im trying to display baseball team stats, along with the ranking for that particular league.
for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20.
I want to show on each individual team page their stat, with the rtanking for the league.