0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
anita_pandey32
  • 1
  • 1
    Welcome to StackOverflow! Please attach your full structure of tables that are needed to perform this task including foreign keys to match the data. Also, decide which DBMS you are using. Answers would be different regarding your engine. Is it Oracle or MySQL? – Kamil Gosciminski Feb 24 '16 at 21:59
  • I think you are wanting a SQL statement that joins your `COLLAGE` and `Employee` tables and ranks the `Salary` for each employee. Not so much to create a column at runtime.. but rather write a SQL statement that will return the record set you need. Also, you need to specify which DBMS you are using. Oracle and MySQL are two very different databases. – JNevill Feb 24 '16 at 22:00
  • If you are having to create columns in your table at runtime, this should be a big flag regarding your design. – OldProgrammer Feb 24 '16 at 22:00
  • Hi i have table collage in which collage(COLLAGE_ID,EMPLOYEE_ID,DEPARTMENT ) is present and EMPLOYEE(EMPLOYEE_ID,SALARY,AGE ) now i need to create RANK while run time which entirely depends on salary. Here EMPLoYEE_ID is the foreign key . – anita_pandey32 Feb 24 '16 at 22:07
  • MYSQL server am using . – anita_pandey32 Feb 24 '16 at 22:08

1 Answers1

0

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.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • Will the current rank will be automatically will get increased ? – anita_pandey32 Feb 24 '16 at 22:10
  • Added some explanation. It will be automatically increased for each row. No, it can't be done using CASE statement since for that you'd need previous value to compare it with. You can also extend this query using http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – Kamil Gosciminski Feb 24 '16 at 22:12
  • See if it works for you, you can also accept/upvote the answer if you find it useful. Here's a link to provide you with information of how to do it : http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Kamil Gosciminski Feb 24 '16 at 22:16