| Author |
Message |
rajasekar
Forum Regular
Joined: 30 May 2004
Posts: 445
Location: Nothing but Net
|
 Posted: Wed Dec 19, 2007 11:25 am Post subject: Find rankings |
 |
|
Hello all,
I need a php code/query to find out the rankings of the records ina a database table in the format "2 of 5". Here 5 is the total records in the table and 2 is the order of that particular record.
eg:
| Code: | 1 | user A | $12
2 | user C | $15
3 | user P | $8
4 | user K | $17
5 | user H | $22 |
This will be ordered in ASC amount:
| Code: | 5 | user H | $22
4 | user K | $17
2 | user C | $15
1 | user A | $12
3 | user P | $8 |
Hence the "user K" has rankings like "2 of 5". Please provide me a code/query for this task. Thank you for your time.
_________________
Free Online Classifieds - Reach thousands of users world-wide!
[img:0a37bba199]http://www.freelineads.com/banners/banner01.gif[/img:0a37bba199] |
|
| Back to top |
|
| |
LP-SolidRaven
Dictator of the Dump
Joined: 06 Jun 2004
Posts: 7182
Location: The cheese is made out of moon
|
| Posted: Wed Dec 19, 2007 12:45 pm Post subject: |
|
|
| Code: | | SELECT id, user, money FROM tablename ORDER BY money ASC |
Seriously, by now you should know that rajasekar.
_________________
| Quote: |
<bart416> I just realized something
<bart416> we celebrate the fact that this piece of rock made one rotation around a glowing ball of plasma that is kept together due to its own gravity well
<njsg> HAPPY NEW YEAR
<Easter> ^^
|
|
|
| Back to top |
|
| |
rajasekar
Forum Regular
Joined: 30 May 2004
Posts: 445
Location: Nothing but Net
|
| Posted: Wed Dec 19, 2007 10:38 pm Post subject: |
|
|
| SolidRaven wrote: | | Code: | | SELECT id, user, money FROM tablename ORDER BY money ASC |
Seriously, by now you should know that rajasekar. |
Hi Solid, Thank you for responce. But, I need how do i get the value "2" in the order?
4 | user K | $17
The string "2 of 5" , I got the "5" from the max rows of the database. The problem is how do i know the position of required record?
Please help me.
_________________
Free Online Classifieds - Reach thousands of users world-wide!
[img:0a37bba199]http://www.freelineads.com/banners/banner01.gif[/img:0a37bba199] |
|
| Back to top |
|
| |
LP-SolidRaven
Dictator of the Dump
Joined: 06 Jun 2004
Posts: 7182
Location: The cheese is made out of moon
|
| Posted: Thu Dec 20, 2007 1:51 am Post subject: |
|
|
Well, it really depends in what context you'll use it.
If it's in a ranking it's fairly simple. You have two methods in that case, First one is simply letting the output script do it. Second one is letting mysql add the numbering. My MySQL is kind a rusty (I've been working with Postgre & oracle lately) but I'll give it a try anyway:
| Code: |
set @rankval = 1;
SELECT id, user, money,
@rankval := (@rankval + 1) as rank
FROM tablename ORDER BY money ASC
|
_________________
| Quote: |
<bart416> I just realized something
<bart416> we celebrate the fact that this piece of rock made one rotation around a glowing ball of plasma that is kept together due to its own gravity well
<njsg> HAPPY NEW YEAR
<Easter> ^^
|
|
|
| Back to top |
|
| |
exsanguination
Forum Regular
Joined: 27 Apr 2005
Posts: 418
Location: Australia
|
| Posted: Mon Jan 07, 2008 4:26 pm Post subject: |
|
|
SQL Server 2005 has a handy way of doing this
| Code: | SELECT ROW_NUMBER() OVER (ORDER BY SortColumn) AS RowNumber, OtherStuff
FROM MyTable |
But it can be done in MySQL as follows:
| Code: | SELECT tablename.id, tablename.user, tablename.money, @num := @num + 1 as RowNumber
FROM tablename, (SELECT @num := 0) as Counter
ORDER BY tablename.money; |
|
|
| Back to top |
|
| |
|
|
|
Forum FAQs 
Knowledge Base 
Memberlist 
Usergroups 
Hosting
Register
Donate 
Profile 
Log in 
