Created
December 23, 2018 16:23
-
-
Save Kcko/8977eb1098c79323316b9339d61d3121 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank | |
FROM testdata | |
WHERE gender = (SELECT gender FROM testdata WHERE id = 6) | |
AND age < (SELECT age FROM testdata WHERE id = 6) | |
SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank | |
FROM testdata | |
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender | |
GROUP BY testdata.id | |
SELECT a.first_name, | |
a.age, | |
a.gender, | |
count(b.age)+1 as rank | |
FROM person a left join person b on a.age>b.age and a.gender=b.gender | |
group by a.first_name, | |
a.age, | |
a.gender |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment