| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-24-10, 09:01
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 3
|
|
|
How to find position in Mysql
|
|
Hi, Guys: I have a table in MySql database which contains marks of students and their classes. Eg. (Jonas, 86, B2)(John, 67,B3). I need a query in sql or mysql which will not only arrange the marks in ascending order but also add the positions in class. Eg(Jonas, 86, 1st), (Evans, 85, 2nd) etc. Please I need y'all help.
|
|
08-24-10, 09:44
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Code:
SELECT name
, class
, mark
, ( SELECT COUNT(*) + 1
FROM daTable
WHERE class = t.class
AND mark > t.mark ) AS rank
FROM daTable AS t
ORDER
BY class
, mark DESC
|
|
08-24-10, 11:03
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 3
|
|
|
|
Thank you for your help.When i run the query, the rank is always the same. it doesn't change or decrease as expected.
|
|
08-24-10, 11:07
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,246
|
|
or do this in your front end, where you display/report this information
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton 
|
|
08-24-10, 11:19
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Quote:
Originally Posted by saintjab
When i run the query, the rank is always the same. it doesn't change or decrease as expected.
|
then you must be doing something wrong, because what i gave you works just fine --
Code:
CREATE TABLE daTable
( class VARCHAR(9)
, name VARCHAR(37)
, mark TINYINT
);
INSERT INTO daTable VALUES
('B1','Curly',84)
,('B1','Larry',82)
,('B1','Moe' ,86)
,('B2','Tom' ,81)
,('B2','Rick' ,84)
,('B2','Hairy',87)
,('B2','Jonas',86)
,('B3','John' ,67)
;
SELECT name
, class
, mark
, ( SELECT COUNT(*) + 1
FROM daTable
WHERE class = t.class
AND mark > t.mark ) AS rank
FROM daTable AS t
ORDER
BY class
, mark DESC
;
name class mark rank
Moe B1 86 1
Curly B1 84 2
Larry B1 82 3
Hairy B2 87 1
Jonas B2 86 2
Rick B2 84 3
Tom B2 81 4
John B3 67 1
|
|
08-24-10, 12:01
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 3
|
|
Thanks r937. It is now working. there was a slight mistake somewhere. You have made my day.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to find position in Mysql
