MYSQL :: Sort And Give Ranking

Give On Condition Higher Priority
I have a system where my pages are built of a bunch of boxes. Each box can have multiple contents, which is displayed att different times. So a box can have a box contents with "begins" = NULL, and "ends" = NULL. At the same time, it can have a box contents with "begins" = 2007-10-01, and "ends" = 2007-11-10.

When I select it all, I group on box id, because I just want one box contents. But the problem is that I need the contents which doesn't have begins/ends as NULL to get a higher priority than the once that do. So a user can have one box contents to be displayed all the time, except from 2007-10-20 to 2007-10-21.

My current query (it's stripped, I don't use SELECT * ) looks like:

SQL
SELECT
*
FROM
pages AS p
LEFT JOIN
pages_boxes AS pb
ON pb.page_id = p.page_id
LEFT JOIN
boxes AS b
ON pb.box_id = b.box_id
LEFT JOIN
boxcontents AS bc
ON b.box_id = bc.box_id
WHERE
( bc.box_ends > CURRENT_TIMESTAMP OR bc.box_ends IS NULL )
AND
( bc.box_begins < CURRENT_TIMESTAMP OR bc.box_begins IS NULL )
GROUP BY
b.box_id

But it always just selects the box contents which was inserted first.

How To Give 4 Default Values To A Column
I need to create a table in which a column is to be specified with 4 default values. Is it possible?

Not Able Give Default Value For DATE Field
I would like to specify a default value for "DATE" datatype as a system
date. But i could find from the help tutorial, "default value cannot be
added for a DATE datatype". But i could specify in Oracle. I have found
below statment from the help documents.

"The DEFAULT value clause in a data type specification indicates a default
value for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means, for
example, that you cannot set the default for a date column to be the value
of a function such as NOW() or CURRENT_DATE. The exception is that you can
specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."

Why cant we use a functions or variable values in date? Is this the
limitation or didnot explore this functionality yet?
Is this limitation applicable for only DATE type or any other datatypes?

I have such a situation to use this "default" feature for a date column
with value as system date.

is there a way to do that?

Updating Give Unexpected Math Results
If I do the following I get a ridiculously huge value out of range on a MEDIUM INT (7) field that is SIGNED:
UPDATE some_table, other_table SET some_table.amount = some_table.amount - other_table.quantity_lost WHERE some_table.id = other_table.id

Why is this happening? I am expecting maybe a -3 if the 'quantity_lost' is 5 than the 'amount' is 2 but instead I get a number like 1654681313879138538515468.

Show All Entries, Then Give An Edit Or Delete Option
I want to show all the entries that are in a database. below each of them I want a radio button that when checked will either allow them to edit or delete the entry. How might I do this?

Sort With Limit But Sort ALL Results.
I have a query that limits by 50 results at a time and I also have an order on a int field. The issue I'm having is it's only ordering by the 50 it returns, how can I have it order by the entire results set and then give me back only 50 results?

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 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.

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 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 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.

Sort
I have a table "users" with fields like:
first_name, last_name, photo_name, date

"photo_name" values are either NULL or
something like
"2_50_tim45.jpg"
"3_50_franky70.jpg"
"4_50_john_long.jpg"

Meaning not all users submitted their photos and those I would like to show at the end
of my HTML table.How do I make a query so that I would get resultset

that would show first users that have "photo_name" and then they would be sorted by
date.

SELECT * FROM users ...

Sort
I would like to know if it is possible to order the results by the quotient of to numbers.For exampel:

$sarray=mysql_query("SELECT * FROM table ORDER BY (rating/votes) DESC");
anyone have a clue how to get it to work?

Keep Sort
The website is an ecommerce site that provides customers with the ability to "browse" the product line. In other words they can move from one item to the next until they get to the last item.
The question is: How can I insert a new item(row) and position it somewhere in the middle of the table? Currently, the table is sorted by the primary key which is numbered 1, 2, 3, etc. (whole numbers). Obviously I can't assign the same primary key to two items. My only thought is to use decimals in the primary key instead of whole numbers. So a row entered between 1 and 2 would be 1.1, 1.2, etc.
Does this make sense? Is there an easier way?

Sort Certain Value
I am displaying results from a table. One row, "position" contains values such as "President", "Vice President", or "None" (many of these).
Is there a way to order the results, alphabetically, by the "position" column, but sort any rows with a position of "None" to the bottom?

Sort Something
sort a list by name and birthplace but I don't really know how to do that. Can anyone give me an example?

Sql Sort
Code:
$sqlquery = "SELECT * FROM $ssl_table WHERE user='$login' ORDER BY date DESC, ticket_host DESC";

I'm using this query and want to sort a list by date and then by Alphabetical order for the teicket_host.

Yet it out puts like this:
2007-01-19 BH
2007-01-18 HM
2007-01-18 HM
2007-01-18 BH

The date is good but the 2nd Sort is not doing Alphabetically. I want it to go:
2007-01-19 BH
2007-01-18 BH
2007-01-18 HM
2007-01-18 HM

SORT BY
I have a very large table that is always sorted by the primary key when I SELECT data from it. Actually, the very purpose of the data being in a database is so that it can be sorted like that. I was wondering, however, if there's some way to use the WHERE clause in SELECT (or a seperate token) to actually give me, say, the 10000th entry if it were to be sorted. Something along the lines of:
SELECT name FROM data WHERE %place% = 10000 SORT BY income DESC
So basically if I had a database of names and their incomes in no particular order, I would need to find the name that has the 10000th highest income.

Automatic Sort
Is there a way to automatically sort "inserts" according to date in mysql? I have searched high and low for something like this in mysql. I know that it can be done in MS sql but cant find it for mysql.

Sort By Date
Sorry i want to show the records if the 'maxdate' is equal or greater than curdate....

No Sort Order?
I have a SELECT statement such as:

SELECT category, title, pubdate FROM ds WHERE category = 3 OR category = 5 OR category = 4 OR category = 101 OR category = 7

I want the result set to return the results in the order that I have queried above (ie: 3,5,4,101,7) but MySQL seems to sort the category so I get the results as (3,4,5,7,101).

Other than splitting into 5 SELECT statements, is there any way I can use a query to say "DO NOT SORT"??

Sort By 2 Colums As 1
I have 2 tables lets say called: files and notes
files has 2 fileds filname and date_added
notes has 2 fields note subject and date_added

if files had 2 records:
1) file1.pdf : 2007-06-14
2) file2.pdf : 2007-06-17

if files had 2 records:
1) great day out : 2007-06-15
2) why me? : 2007-06-18

i want to sort by the date in both fields but as if they were in 1 fields so output would be
2007-06-14
2007-06-15
2007-06-16
2007-06-17
and not
2007-06-14
2007-06-17
2007-06-15
2007-06-18

Sort Problem
I'm new to mysql and ran into this sort problem.

Say I have these entries and this is the sort order using char data type:

C100
C1000
C150
C200

I need the sort order to be this:

C100
C150
C200
C1000

What data type do I use?

Sort Aborted
I am getting Error 1028 Sort Aborted, is there any information available that may help me determine the cause of this error.

How To Sort In Mysql By Own Needs
filename: p1.pdf, p2.pdf, p3.pdf...pN.pdf in database column. select filename from filename from table order by filename -- the returned result won't be in the filename order.

But If I change the filename to p001.pdf, p002.pdf, etc. It will work fine. Is there any way to make it work in the 1st scenario? i.e. sort in filename order.

Sort With A Limit
What I need to do is display results that are sorted based on user preference. These results need to be paged to display 25 results per page. Currently I'm using a simple sort and limit. However, as many of you smart folks know, MySQL only sorts the values it finds within the limits as opposed to sorting the whole table and then limiting results.

SORT BY With Condition?
is there a way to do sorting depence of some condtion directly in the MySQL query?
for example, I have this query:

SELECT cat_id, cat_name, cat_date, cat_parent
FROM categories
ORDER BY cat_name ASC
but I'd like change sorting if cat_parent = 23 for example.
so, is there a way use something like:
SELECT cat_id, cat_name, cat_date
FROM categories
ORDER BY ((IF cat_parent = 23) THEN cat_date ELSE cat_name) ASC

I dont know MySQL at all, so this is just an example of condition idea that I'd like to use.

Sort A List
how can I sort my database by the age of the people that I have in the database and the place they live.

Natural Sort
how is possible sort rows in table like php function natsort - http://php.net/manual/en/function.natsort.php Values in my table.

- "35 - somethig"
- "51 - somethig"
- "06 - somethig"
- "01 - somethig"
- "02 - somethig"
- "08 - somethig"
- "ZC"
- "BA"
- "AA"

I need this order
- "AA"
- "BA"
- "ZC"
- "01 - somethig"
- "02 - somethig"
- "06 - somethig"
- "08 - somethig"
- "35 - somethig"
- "51 - somethig"

Not Sure To Use Filer Or Sort
I have fields Date, Exporter, Importer, Waybill, Agent, Destination in my table.
I wish to sort or filter by Date and Agent only. but it must give all the info regarding that criteria. I've tried GROUP BY but it only consolidates the info. My Date column is date and not varchar. My date column is yyyy-mm-dd.

Sort NULL Last
I have a mysql query, "SELECT * FROM task ORDER BY duedate ASC", where
"duedate" is a unix_timestamp and can be NULL. I want any NULL results
to be at the end of the list, how might I do this?

Array And Sort
I have products that belong to certain categories. I want to array the product (mysql_fetch_array), and ORDER BY the Category they belong to. However I dont want the category to array each time.

I want
CATEGORY ONE | CATEGORY 2
product one | product one
product two | product two

The code i am using is --

$query ="SELECT category.cat_id, category.catname, inventory.cat_id, inventory.product, FROM category INNER JOIN inventory ON category.cat_id = inventory.cat_id ORDER BY catname ASC";
.................
while($row = mysql_fetch_array($result)){

echo "<tr><td>";
echo $row['catname'];
echo "</td><td>";
echo $row['product']; ETC

How can i array the products with out the Category, but have category as a heading??? where the arrayed products will go under.

Sort Aborted
I am getting the error "Sort Aborted" when executing the query "SELECT object2, userid from users1 ORDER BY object2;"

Sort Asc W/ Most Recent
I want to display the five most recent rows in my table (there is a timestamp), but sorted in ascending order.

the problem is that when i call

SELECT * FROM spam ORDER BY time ASC LIMIT 5

it shows the first five entries in ascending order, but i want the last
five.

needless to say, it works fine with descending order..

Group And Then Sort
Hi all i have a query that i did that is working fine as in so far of gathering the data that i am after , it is the following.

$result = $DB->query( "SELECT name, AVG(speed), COUNT(*) FROM readings group BY `$srtby` DESC LIMIT $maxshow");

what i am trying to also do is have the results sorted by speed , is it possible to both group by as well as sort by , i have tried numorous combinations but cannot seem to get it to work.

SQL Sort (Order By)
I have sql statement below ,

SELECT
serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size,
country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3
FROM tbl_chassis ORDER BY country = '', country

However , my user would like to sort by country,city,building and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,building,other gave me the result which i not expected.

For example below data before sort

Country City Building Other
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 83 Aber rd
Thailand Bangkok
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark

After sort

Country City Building Other
Thailand Bangkok 83 Aber rd
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok

Predicting SORT BY
I have a very large table that is always sorted by the primary key when I SELECT data from it. Actually, the very purpose of the data being in a database is so that it can be sorted like that. I was wondering, however, if there's some way to use the WHERE clause in SELECT (or a seperate token) to actually give me, say, the 10000th entry -if it were to be sorted-. Something along the lines of:
SELECT name FROM data WHERE %place% = 10000 SORT BY income DESC.

Sort List By Day
ive got a list of days i.e. monday, wednesday, friday etc. in random order.
The problem is i want to sort the list by the day of the week, so monday first then tuesday, wednesday etc. is it possible to do this?

Sort Of Like Distinct
Is there a way to retrieve a set number of duplicate results?
For example, I have a calendar application and I want to retrieve only 4 results per day.
The data is like this:

Woo, 6
Yes, 6
Foo, 6
Bar, 6
Yay, 6

where those 5 entries are from the 6th of the month, so basically I want to limit each distinct date to 4 results.