getting row data + its position

[asmith20002]

Hi,

I have a table about members points with some other information.

I want to get the entire row fields + the row position number ONLY for an specific member.
for example, member 'john' has 30 points and it will be the 3rd row if I select the tables data order by points.

I want to get all john data + a field that it says he is 3rd.

"row position" brought up a lot of articles in google. but I couldn't find this one.
I think it would be a bad query if I manually select all the table data, and then set a counter by PHP to get my desired row position since I don't need other data.

How can I achieve this?

p.s :
I found this query, But I have no idea how it works :

SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

[xvszero]

Hmm. I don't know any specific way offhand (but I am a n00b.) One thing I used to do for keeping track of places and such is have a "place" field for every member in the database and whenever points change, a function goes through and reorders the places based on that change. But if points are changing all the time this could use up valuable resources, I suppose. The site I did this with was pretty small so it didn't matter.

Probably a better way though.

[chazzy]

depends on what dbms you're using. for example, oracle has the ROWNUM variable that can be used in a query.

[asmith20002]

I'm using MySQL 5.0

[chazzy]

ok.. thanks for the note

So you got this query...


SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r


Which is almost right. I think the typical mysql example is:


SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;


I.E. no AS and the rownum set comes first. This sets for each row returned the column "rownum" in your result set to the position a particular row is in the result set.

[felgall]

Unless you are using ORDER BY you don't know where the row will be in terms of its order since the database is allowed to store the rows however it likes.

To work out that something is third highest you'd need to count the entries that are greater and add one. If you want that count in a field where it updates automatically then you'd create a view that contains that calculated value as one of the fields.

[asmith20002]

Thanks for the replies.


SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;

made me so excited, but still it won't work. Using this :

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t order by points desc

works fine and gives me all the members and the "rownum" will be exactly what I want. But again it is giving me ALL the members. SO I have to :


<?php
while ( $info = mysql_fetch_assoc($result))
{
if ($info['member'] == 'DESIRED_MEMBER')
{
$hisInfo = $info;
}
else
Ignore the row
}
?>



Now If I run this query to only get my desired member :


SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t where member = 'DESIRED_MEMBER' order by points desc

then the rownum will be 1 (logic enough, mysql couldn't count more rows)
Is there a solution for this?

I thought about making a temporary table of the result, then select my member from that, But I guess going through a while loop with php will be a faster way.

Any idea?

( what is the name of := operator? I don't how to find it in the document. what does @ do in the query?)

[chazzy]

no, because ROWNUM on a query like:

select * from my_table where my_pk_or_unique_column = somevalue; is always 1.

i have a feeling you're attempting to use an SQL order by clause to do some application logic without realizing that maybe you do just need to add in some application logic to give everyone a rank.

[asmith20002]

ok, How do you solve this :

In users profile page, I wanna show their position, for example :
"2nd, With 3000 points (56 won)"

in the points table i have this :
username won points
mat 65 3200
john 56 3000
pete 50 2800
.
.
.


how do you get the row "john" position (when ordered by points) AND his
"won" and "points" column values ?