mysql: get row index for an individual result for total rows with a given category -

i'm not sure how should have worded title, here i'm needing:

i have function pulls 1 particular row table id. row contains category id well, , "order by" number , "order suffix" alpha-numeric field. (so ordered 1, 2, 2a, 2b, 3, 4, etc.)

what need pull row, find out row number if pulled of rows category in , ordered order number , order suffix.

so, if had:

select id, category, order, suffix table id = 28;

id: 28, category: 4, order: 2, suffix: b

going off previous example of entries in same category order of 1, 2, 2a, 2b, 3, 4, need know particular item 4th item in category.

of course, know second query pull rows category , order them correctly, loop through them , find out 1 matches item, wondering if there better way.

**all example data , fields that: examples. know example field names bad, etc. ;)

edit

fiddle: http://sqlfiddle.com/#!9/348d8/7/0

i had use 1 query results needed , ended using:

select items.*, @category_id := items.category_id,    (select t1.rank       (select t.item_id, @rank:=if(@rank, @rank+1, 1) rank         items t         @category_id = t.category_id         order t.order_num asc, t.order_suffix asc) t1    items.item_id = t1.item_id) rownum items items.item_id = 7; 

so started writing simple query selected each order number , ordered in ascending order, along variable adds row number us:

set @rownum := 0;  select (@rownum := @rownum + 1) rownum, concat(ordernum, suffix) ordernum mytable order concat(ordernum, suffix); 

then, can use subquery row num 1 need:

set @rownum := 0;  select rownum from(   select (@rownum := @rownum + 1) rownum, id   mytable   order concat(ordernum, suffix)) tmp id = 28; 

here sql fiddle example returns expected result of 4.

edit:

since can have multiple categories, need update our subquery run on necessary category. unfortunately, cannot reference outer query inside of our subquery, need add simple 1 grab category_id this:

set @rownum := 0; set @searchid := 7;  select rownum from(   select (@rownum := @rownum + 1) rownum, item_id   items   category_id = (select category_id items item_id = @searchid)   order concat(ordernum, suffix)) tmp item_id = @searchid; 

note instead of writing 7 twice , having change param each time added variable.

here updated fiddle link.