CODEWITHSUNDEEP
mysqlsql
Josiah
Josiah

Reputation: 3326

MySQL: get a rows position within a sorted table within a view

Ok, I am having trouble think how to describe this problem, but basically I have a view that looks like this:

CREATE VIEW `dbname`.`v_viewname` AS
select
idTable,
round(val1*(probability*.01),2) as probest,
from table

And what I want to do is create another field (let's call it "rank") that is the position of the row when it is sorted by probest in descending order. First I created a stored procedure which will generate a table which does this and turns it into a derived table, which look like this:

set @rownum := 0;

select * from (

select @rownum := @rownum+1 AS rank, idTable

from table order by (val1*probability) desc )

as derived_table;

so the question is, how would I join this derived table with my the table I read from in my view? Were the table in a routine or something I would use:

table join inner derived_table on table.idTable = derived_table.idTable;

but because this has to be done through a view, I don't even know how I would get the derived table for joining. Also: If this way won't work or is inefficiency in any way, what would be an alternative method?

Upvotes: 0

Views: 255

Answers (1)

Imre L
Imre L

Reputation: 6249

select tbl.*, @rownum := @rownum+1 AS rank
from (select
      idTable,
      round(val1*(probability*.01),2) as probest,
      from table
      order by probest desc) tbl
      ,(select @rownum := 0) init_vars

Upvotes: 1

Related Questions