CODEWITHSUNDEEP
phpmysqlsql
e.klara.k
e.klara.k

Reputation: 379

Get the position of a row after sorting mysqli result

I am looking for a way to get the place of a specific item after sorting my mysqliresult. I have a table like this:

id_______|_Name_____________|_ParentID
58       | Carl             | 15
55       | Clark            | 15
12       | David            | 4
23       | Sophie           | 15
45       | Amanda           | 15

I am only interested in the rows with ParentID 15 and I want to sort them by their Name. Then I want to look at a specific item, say id 55 (Clark) and know which row number it is placed on. I want to generate a table like this:

id_______|_Name_____________|_ParentID
45       | Amanda           | 15
58       | Carl             | 15
55       | Clark            | 15
23       | Sophie           | 15

I then want to get the number 3 when I am interested in Clark, since his row is the third row in this new table.

Is this at all possible to do with mysqli? If it can't be done in a single statement, I have to loop through all selected items and have a count that increases for every item until I find my correct one but I would really like to avoid this since the number of items to loop through quickly increases.

Upvotes: 3

Views: 299

Answers (4)

valex
valex

Reputation: 24134

In you can use User-Defined Variables

SELECT T.*,  if(@a, @a:=@a+1, @a:=1) as rownum 
FROM T
WHERE ParentID=15
ORDER BY Name

SQLFiddle demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can get the row for Clark by doing:

select count(*)
from t
where t.ParentId = 15 and
      t.Name <= 'Clark';

Indexes t(Name, ParentId) will speed performance.

Upvotes: 0

Mubashar Abbas
Mubashar Abbas

Reputation: 5663

Try

SELECT * FROM table_name WHERE parent_id = 15 ORDER BY name ASC;

Upvotes: -1

sagi
sagi

Reputation: 40481

Try this:

SELECT t.*,
       (SELECT COUNT(*) FROM YourTable s
        WHERE t.parent_id = s.parent_id 
           and t.name <= s.name) as Cnt
FROM YourTable t

Output :

id_______|_Name_____________|_ParentID__|_Cnt
58       | Carl             | 15        |  2
55       | Clark            | 15        |  3
12       | David            | 4         |  1
23       | Sophie           | 15        |  4
45       | Amanda           | 15        |  1

Now Cnt column contain the row position, so to get any one of their positions:

SELECT tt.cnt 
FROM (...above query here...) tt
WHERE tt.name = ? --optional
  AND tt.parent_id = ? --optional

Upvotes: 2

Related Questions