MYSQL :: Sorting And Put Ranking In Field

Sorting DATE Field By Year
I've have a field ('gamedate') in a table ('games') that is in DATE format, but what I can't figure out is how to use the year as part of a search form option. I'm hoping to get a drop down menu that is a list of years, that when selected will bring back just the records from that year, or in the future, use BETWEEN to find all records from a set group of dates.

Is there a step I'm missing to "extract" the "YYYY" part of the DATE field before using it? Or maybe there's a wildcard that needs to be stated to get "MM" and "DD" to equal "any"?

Sorting Numbers In A VarChar Field
I have a table that has a small alpha-numeric varchar(5) field in it. Field is used as a key for some diagrams. Some of the diagrams are labeled with numbers only (ex. 1,2,3,4...) and others are labled with alpha numerics (ex. A1,A2,B1,B2...).

I'm running into a problem sorting the field properly. When I sort a query that contains only numbers I get results like the following:
CODE1
11
12
13
2
3

Numeric Sorting Of A String Field
I have an issue with sorting sail numbers for sail boats. Sail numbers may optionally contain a country prefix (can5003, or US345), hence the string. So.. I need to sort the sail numbers numerically by the integer component only (ignorning the chars).

Sample data
1352
US 2005
74272
CAN801

Needs to look like this.
CAN801 - sorted as 801
1352 - sorted as 1352
US 2005 - sorted as 2005
74272 - sorted as 74272

I don't quite know where to start.. a generated field that is 0 lpadded to some max length? - not sure how to drop the chars.

Sorting By Latest Date Field In Each Record
I have a MySQL table with the following fields:

ID (int 11) (PK)
Company (varchar 50)
Contactdate (date)
Formdate (date)
Signdate (date)

I would like make a select query that orders the records by the most
recent date (i.e. descending) of any of the three date fields and then
by company name. Some date fields may be empty (null) A record must not
be listed more once.

For example, if I have the following data:

ID, Company, Contactdate, Formdate, Signdate
1, acme1, 2006-01-01, 2006-01-10, Null
2, acme2, 2006-01-01, 2006-01-11, 2006-01-12
3, acme3, 2006-01-08, 2006-01-09, 2006-01-09

The result should be:

2, acme2
1, acme1
3, acme3

In other words, the query should determine the 'latest date
field activity' of each record and then order by that date (and then by company).

If it is complicated to use null values I can also set the default
value of the date fields to something like '1900-01-01'.

Sql Querying Two Tables And Sorting By A Date Field..
I have two seperate mysql tables. each has a couple fields that are similar across the tables (for my intended purposes).

I can't sort by uniqueID because they have different id's obviously being in different tables. We do however, have a date/time stamp column in both tables that I'd like to sort by.

i came up with this.. but am not sure if the results i'm getting are accurate..

select artid,img,artist,date,info from cms_artists where info !='NULL' union select articleid,img,subject,date,text from cms_articles order by date desc limit 10;

Sorting On Language-specific Translated Field
I have a table with a numeric field that will be translated to text, depending on language. That text is NOT in a table, just an array.

Is it possible to construct a query, so that the table output can be sorted on that language specific field, using the texts that I give in the query?

What I mean is that the query will take the field, translate the number to text using an array that I give, and then sort on that text, and give me the resulting records?

Or is the only way to put those texts into a table?

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?

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.

Sorting
I have a database with 5 teams and each team has 'n' categories and each
category has 5 sub-categories.

i.e TeamOne = Ottawa, Category 1 = Player Name, Sub-Categories = Goals,
assists, ppg, gwg, points.

Team0ne
Category 1
Sub 1
Sub 2
Sub 3
Sub 4
Sub 5
Category 2
Sub 1
Sub 2
Sub 3
Sub 4
Sub 5
Etc.

From the examples above I am trying to add the all the sub-categories
together for each category for each team in descending order.

To put it simpler - I want a total of all the goals scored for each team
and display them in descending order. I have been able to put a query
together that gets me the totals, but I can not get them in decending
order.

The query below is in a for loop in PHP and cycles through each team and
does give me the totals, just not in descending order (actually the
order is the way they are numbered in the database);

$query = "select team.name, sum(goals) from roster join reference join
team where team.idn=reference.idn and reference.idp=roster.idp and
position = 'F' and team.idn = 1 group by team.name";

Sorting
I have two columns, one is an int and the other is a date, I want the records to show first the null values in the int column and have that sorted by date, then the rest of the rows sorted by date.

I tried doing

order by int asc, date asc, but it only orders the null values properly.

so this should be the result:
date, int
3/12  null
3/13  null
3/8   5
3/9   4
3/10  4
3/11  5

Sorting
I need to sort a query's results from a database and I only want a particular value to be first. So if any of the query results equal "text" (the string) I want them to be the first values returned.

Sorting Twice
I want to sort my query by date then by name. Right now I'm using:

SELECT * FROM `race_calendar` order by date asc

How can I add the second sort?

Sorting
I am currently developing a used car website. When a user browses our inventory it displays the cars to him by the id it was entered, IE when entered into the database each car is assigned a number 1 , 2 , 3 so on. When the browse button is clicked it only sorts the vehicles by Car ID#. Is there anyway I can have it sort by price , or year , or make? Is the sorting in the database or in the scripting?

Sorting By Value
How can I fix this?
Using the following to select and sort:
$query_land = "SELECT id, type, estate, parcel, asking, zoning, `size`, selling FROM sold2005 WHERE type = 'land' ORDER BY estate ASC, selling ASC";

Sorting
Is there anyway to sort in a different order in mysql than it usually does. Here is an example.

m100
m20

Mysql will order them in that order but the problem is that m20 should come before m100.

Not Sorting
some how my query is not sorting ASC or DESC, what am doing wrong?
Code:

SELECT DATE_FORMAT(jdate, '%a. %M-%d-%Y') as jdate, jid, jtitle FROM hrjobs WHERE jstatus ='on' ORDER BY jdate ASC

Sorting
Is there any way to sort a field in mySQL db when items are inputted into the DB, so they are sorted in the actual DB. I know they can be sorted easily in PHP on the client page, but I am not sure if there is any way for the DB to actually sort them. (It would be a numberic field, int).

Sorting
i have a mysql db of about 300-400 users which is used for a game, part of an update i'm working on is rankings, i want to know is there an easy way to sort the results of a SELECT query, so highest score first, lowest score last and then find what position 'user' is within the table?

Advanced Sorting
SELECT
a.asset_id,
CONCAT( a.subcategory_id, '-', a.asset_type_id) AS sort_key,
SUM( av.vote ) as rank
FROM
asset a
LEFT OUTER JOIN
asset_vote av
ON
av.asset_id = a.asset_id
WHERE
a.status = 1 AND
a.school_id = " . $SchoolID . "
GROUP BY
a.asset_id
ORDER BY
sort_key,
rank
LIMIT 100

I am working on a query and we would like to pick 100 assets.

I would like one of each asset_type for each subcategory sorted by the highest rank. If there are only 3 subcategories and 3 asset types, that means I have 9 assets. If we have picked the "top" asset already it should go through the query again and pick the second one.

I need to put 100 assets through, so if we run out, I would like to move to pull more from a different category.

Any help from here? I am thinking of trying a group by on my sort_key?

Query On Sorting
I have a query
SELECT atype, id, login, branch_name, contact_person, address1, address2, city, state, country, zip, telephone, fax, url, logo, branch_description, county FROM vendor WHERE id IN (?',&#55618;&#56996;',?',?',&#55618;&#57036;',?',&#55618;&#57039;',&#55618;&#57062;',&#55613;&#57251;',&#55617;&#56783;')

If the ORDER BY clause is not present mysql sorts by the PRIMARY ID (id in this case)
I want it to be sorted by the ids present in IN (?',&#55618;&#56996;',?',?',&#55618;&#57036;',?',&#55618;&#57039;',&#55618;&#57062;',&#55613;&#57251;',&#55617;&#56783;')

any solution.

SORTING Problem
I am trying to create a query that will order a list of articles by 2 fields.
First, it should order them by the date they were published, and then, it should order them by priority, with the newest articles and highest priority first. (There are multiple different priority 1 articles for example. Each publishing date has its own set of priority numbers from 1 to somewhere in the mid 40's).
I tried a query like:
SQL
SELECT title FROM articles WHERE cat='breakingNews' ORDER BY priority,datePublished DESC
But it didn't give me the results sorted as they should be. After a Google search, I also tried this:
SQL
SELECT title FROM articles WHERE cat='breakingNews' ORDER BY (priority/datePublished) DESC
Yet neither one provides me with the data sorted as I need it. Can anyone help me with this query?

Sorting By Two Columns?
Is it possible to ORDER BY one column ASC and then by another within the first order?

You know like when you do a data sort in excel, you enter your first column and then your "then by" column and that second sort only takes place within the first?

PS: I tried using the search function for this, but was unable to find anything.

Sorting On Date
I want to show a list that is ordered by birhdays, where the person who have birthday soonest will be at the top of the list. Can anyone please give me the syntax for this request.

Sorting Within GROUP BY?
I've been working hard at this for a while, and I've figured a few things out, but I'm having trouble sorting the below query by "mls_condos.date_posted":

CODE"SELECT SQL_CALC_FOUND_ROWS owyw.*,mls_condos.name,MAX(mls_condos.date_posted),COUNT(mls_condos.date_posted)
FROM owyw
LEFT JOIN mls_condos ON mls_condos.name=condo_names.mlsname
JOIN condo_names ON owyw.development=condo_names.mcbname GROUP BY owyw.development
ORDER BY mls_condos.date_posted DESC"

Sorting With Umlauts
I've spent most of today trying to figure out how to get my query to sort correctly. I've read the CHARACTER SET and COLLATE documentation without any luck. I want my data to be sorted like this (it is the correct way to sort in Swedish):

a
b
c
z
Ã¥
Ã¤
Ã¶

With my current settings, however, I get:

a
Ã¥
Ã¤
Ã¶
b
c
z

I have changed the database and tables to the following configuration:

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

I have also tried to query with collate, like this:

SELECT title FROM papers ORDER BY title COLLATE latin1_swedish_ci desc

Still, the data is not sorted as I want it.

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,

Sorting Problem
I have a small problem. I have this sql schema:

`ticker` varchar(10) NOT NULL,
`host` varchar(50) NOT NULL,
`charname` varchar(30) NOT NULL,
`amount` int(10) NOT NULL,
`timestamp` int(15) NOT NULL,
`valid` int(15) NOT NULL,

Now, each "host" has "zero to n" rows, each row lists...

-> ticker (small identifier)
-> host (ticker from above as subdomain.domain.tld)
-> charname (character who paid)
-> amount (how much he/she paid)
-> timestamp (when he/she paid, UNIX)
-> valid (a period, relative to zero, in seconds)

Now, after more than one payment, a host has several rows.
I want to "select" all DISTINCT tickers and their rows where
timestamp is max'ed. So if i have 5 tickers i would like 4
results, each result the most recent (talking in timestamp)
entry into the database.

Sorting On Two Columns At Once
I have a table with 2 columns called "lastname" and "company" that I would like to sort. Sometimes the lastname column is null, and in that case I would like to use the company column for the sort. Is this possible? E.g. this data:

[lastname, company]
smith, amway
jones, IBM
<null>, microsoft

should output the rows in this order:

jones
microsoft
smith

Sorting From Mysql ....
I am pulling data out and sorting on the postcode - but have been asked to put it in 'proper' order - ie non-acii-betically ..

I need -
BB1
BB2
BB10
BB25

NOT -
BB1
BB10
BB2
BB25

Can we do this in the 'select' statement ?

Sorting Enum's
Is there a way to easily sort Enum's in the SQL query by the data rather than their position in the enumeration array?

i.e. I have a field called priority, it holds the values
1-10, A-D, Must, High, Medium, Low, Very Low, ''

(The last being blank string)

It sorts by the order I put them into the enum, which is actually better in this particular case... but I'm curious, if I wanted to sort by the normal alphabetical method on this column, could I?

Grouping / Sorting
However the data is brought back and sorted by month and I was wondering if it's possible to bring back the data the has franid,barid in every month of the year.

So right now, as you can see showing the results for the first 3 months, Month 2 has data in the second row I don't want returned and in any calcultions. The franid, barid combo has to be represented in each and every month, in this particular case between Month 1 and Month 12.

1234, try, 7894.89, 1
1456, tre, 7842.78, 1
3526, gft, 5690.00, 1

1234, try, 6794.89, 2
6789, ght, 7842.78, 2
3526, gft, 5340.00, 2

1234, try, 2394.89, 3
1456, tre, 4542.78, 3
3526, gft, 8990.00, 3

Sorting Top Three From Any Number
Sorting top three from any number

I am doing a quiz.

A player can have as many goes as they like e.g. Fred has 6 goes, Jessica has 5 goes, Ann has 4 goes and they score:

Fred
3
6
3
7
2
4
Jessica
5
4
6
7
3
Ann
3
4
6
5

How do I sort the data alpahbetically and by number so that ONLY the top three scores for each player are shown:

Fred 7
Jessica 7
Ann 6
Fred 6
Jessica 6
Ann 5
Jessica 5
Ann 4
Fred 4

Sorting Addresses
I am working with MySQL to output a list of addresses. What I'm trying to do is sort the output by the address, but instead of sorting by the leading numeric values, I want to first use the alpha values and then the leading numeric values. For example, say I have the following records in a table called USERS in the column Address1:

100 Fifth Avenue
48 Fifth Avenue
19 Seventh Street
123 Ninth Street
20 Eleventh Street

If I use the command "SELECT Address1 FROM USERS ORDER BY ORDER BY Address1 + 0", the result will be this (since MySQL would sort by the leading numbers):

19 Seventh Street
20 Eleventh Street
48 Fifth Avenue
100 Fifth Avenue
123 Ninth Street

What I am instead trying to accomplish would be output that looks like this:

20 Eleventh Street
48 Fifth Avenue
100 Fifth Avenue
123 Ninth Street
19 Seventh Street

Essentially, I need an ORDER BY clause that allows me to first sort by the street name and then will sort each individual house number per street name in increasing order. What can I do to accomplish this without creating a separate column for each street address and street name?

Sorting Problem
I have an issue with a program I'm writing. I need to sort a list of projects by the project number. The problem I'm facing is that the "numbers" are formatted as follows: ###/##-###.#.# and of course if I have one say 500/00-125 and another 500/00-50, the 50 is going to come before the 125. This is a problem for the people using the program. I was wondering if there was any way to sort these numbers better.

I'm just using 'ORDER BY projectnum', as it's the only way I know how to do it.

I'm using MySQL and connecting it to a Delphi program via BDE if any of that matters to you.

Sorting Problem
Lets say you have data like this:

Time            Sessionid
20.55        a1
20.44        a1
20.39        x3
20.33        x3
20.29        a1
20.27        a1

and you want to sort it so it looks like this i.e. grouped according to Sessionid and then Time:

a1 = 20.55 20.44 20.29 20.27
x3 = 20.39 20.33

What is the best way to do this in mySQL?

Sorting (ORDER BY)
in MySQL are rows with these values:

995
25120
991
99
1132
86

I would like to sort them. But when I tried to use this

SELECT * FROM table WHERE something='$something2' ORDER BY numbers DESC

result was this:

995
991
99
86
25120
1132

and that's absolutely wrong!

Result I want is something like that:

25120
1132
995
991
99
86

Multiple Sorting ?
In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean.

I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well.

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?