'creating a column at runtime while runnin the sql query

I have to create a column at run time (RANK of salary ) ,which depends on the value of a salary column from one table(COLLAGE ) and this salary is associated with an employee table. Can you suggest how to generate it . The RANK column will contain the value based on salary i.e if the salary is highest than RANK is 1 ... in ascending order.

mysqlsql


Solution 1:[1]

This would get you id of your employee, their salary and a rank column.

SELECT 
    *, 
    @currentRank := @currentRank + 1 AS rank_of_salary
FROM (
    SELECT
        c.employee_id,
        e.salary
    FROM      
        collage c
        INNER JOIN employee e ON c.employee_id = e.employee_id
    ) t, (SELECT @currentRank := 0) r
ORDER BY salary

(SELECT @currentRank := 0) initializes a variable so that you do not need separate SET statement.

For each row @currentRank variable is being increased and stored in rank_of_salary column. It's actually more a row_number equivalent I believe. Proper ordering of this rank is maintained by sorting the output with ORDER BY salary clause.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions