![]() +-+-+-+-+-+-+Ĭombination of Daniel's and Salman's answer. | id | score | rank | percentile | studentNumber | | Results of the query for a sample data - +-+-+-+-+-+-+ SELECT id, score, := AS := - + := + 1 as marksheets, ( Also two people with same marks will get the same rank. | id | firstname | gender | age | rank |Ī tweak of Daniel's version to calculate percentile along with rank. PS: It can easily be converted to row number over partition by by removing all conditions that check for tie. The IS NOT NULL is added to handle datatype conversion and short circuiting issues. This (in theory) takes care of order of evaluation issue. Notice that the variable assignments are placed inside the CASE expression. WHEN := gender) IS NOT NULL AND := age) IS NOT NULL THEN 1įROM person, (SELECT := NULL, := NULL, := NULL) AS x WHEN = gender AND = age THEN = gender AND := age) IS NOT NULL THEN + 1 INSERT INTO person (id, firstname, gender, age) VALUES It uses user variables: CREATE TABLE person ( Here is a generic solution that assigns dense rank over partition to rows. INSERT INTO person VALUES (8, 'Anne', 25, 'F') INSERT INTO person VALUES (7, 'Steve', 36, 'M') INSERT INTO person VALUES (6, 'Kathy', 18, 'F') INSERT INTO person VALUES (5, 'Nick', 22, 'M') ![]() INSERT INTO person VALUES (4, 'Bill', 32, 'M') INSERT INTO person VALUES (3, 'Jack', 30, 'M') INSERT INTO person VALUES (2, 'Jane', 20, 'F') INSERT INTO person VALUES (1, 'Bob', 25, 'M') Test case: CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1)) The (SELECT := 0) part allows the variable initialization without requiring a separate SET command. One option is to use a ranking variable, such as the following: SELECT := + 1 AS rank
0 Comments
Leave a Reply. |