find a specific row in a set of rows

[Dormilich]

Hi,

I have a table where I want the position of a row in a result set that meets an additional condition.

+----+-------+------+
| ID | valid | time |
+----+-------+------+
|  1 |   0   | 1:00 |
|  2 |   0   | 2:00 |
| 18 |   0   | 3:00 |
| 24 |   0   | 3:30 |
| 39 |   1   | 3:55 |  <-- need that position where `valid` = 1, i.e. "5"
| 46 |   0   | 7:00 |
+----+-------+------+

from the listed table above (which is already the result of a JOIN) I want the position of the marked row within the result set (the result set is already orderd by the `time` field).

I looked through the MySQL function reference, but nothing looked like it would do the job, so does anyone know how to do this?

[Dormilich]

I learned that I cannot capture all values I want when using JOINs, so I have to make sequent queries.

to get the desired value I count all items with a time or ID lower or equal to the row of interest (still untested, though).

[chathura86]

you can use a sequence row to number the record set and get it

eg

let say you got the above result set from query X

select rownum from
	(SELECT @rownum:=@rownum+1 rownum, tb1.*
	FROM 
		(SELECT @rownum:=0) r, 
		(X) tb1 ) T
where (T.valid > 1)

Regards

[Dormilich]

could you explain that SQL or point me to an explanation of it, please? I’ve never seen such a syntax.

[chathura86]

(SELECT @rownum:=0) r

initialize the @rownum (mysql variable) to 0

(X) tb1

create a temporary table from your sql and name it as tb1

 SELECT @rownum:=@rownum+1 rownum, tb1.*
     FROM 
         (SELECT @rownum:=0) r, 
         (X) tb1

this will select all from tb1 and for each row you will have the row number
because oyu are increasing the @rownum by 1

so now you have your result set with the row number


make a temporary table from the above sql and name it as T

select rownum from
    (SELECT @rownum:=@rownum+1 rownum, tb1.*
    FROM 
        (SELECT @rownum:=0) r, 
        (X) tb1 ) T
where (T.valid > 1)

now you can use it as a regular table for filtrations etc.

hope this will help you.

by the way did it solve your problem?

Regards

[Dormilich]

by the way did it solve your problem?

I’m busy this whole weekend, so I can tell you next week at the earliest.