{"id":108,"date":"2011-06-19T20:48:20","date_gmt":"2011-06-19T20:48:20","guid":{"rendered":"http:\/\/62.131.51.129\/wordpress\/?p=108"},"modified":"2011-06-19T20:48:20","modified_gmt":"2011-06-19T20:48:20","slug":"ranking-the-rows","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=108","title":{"rendered":"Ranking the rows"},"content":{"rendered":"

I was asked a few days to write a SQL that would retrieve the one but latest row. Take as an example a few rows below: we have a several functions with their min salary. The question is: which function earns the one but highest minumum salary. In the rows below, we have minimum salary 20000, 15000, 3000 and 8200. The outcome would be the row where the minimum salary 15000 is found as we should disregard the highest number (here 20000). <\/p>\n\n\n\n\n\n\n
JOB_ID<\/th>\nJOB_TITLE<\/th>\nMIN_SALARY<\/th>\nMAX_SALARY<\/th>\n<\/tr>\n
AD_PRES<\/td>\nPresident<\/td>\n\n
20000<\/div>\n<\/td>\n
\n
40000<\/div>\n<\/td>\n<\/tr>\n
AD_VP<\/td>\nAdministration Vice President<\/td>\n\n
15000<\/div>\n<\/td>\n
\n
30000<\/div>\n<\/td>\n<\/tr>\n
AD_ASST<\/td>\nAdministration Assistant<\/td>\n\n
3000<\/div>\n<\/td>\n
\n
6000<\/div>\n<\/td>\n<\/tr>\n
FI_MGR<\/td>\nFinance Manager<\/td>\n\n
8200<\/div>\n<\/td>\n
\n
16000<\/div>\n<\/td>\n<\/tr>\n<\/table>\n

To this problem, we have two solutions:
\nOne solution is:<\/p>\n

\nSELECT rownum, min_salary from \n(select min_salary from jobs order by min_salary desc)A ;\n<\/pre>\n
Here, we order the table first, then return the rows with the rownumber. This could then be filtered on rownumber=2.
\nThe second solution is:<\/p>\n
\nSELECT min_salary,\n       RANK() OVER (ORDER BY min_salary desc) \"rank\"\nFROM   jobs;\n<\/pre>\n
This makes use of the so-called analytical functions. It leads to the same result: after a filter on RANK()=2, we have the desired row.<\/p>\n","protected":false},"excerpt":{"rendered":"
I was asked a few days to write a SQL that would retrieve the one but latest row. Take as an example a few rows below: we have a several functions with their min salary. The question is: which function earns the one but highest minumum salary. In the rows below, we have minimum salary […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-108","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=108"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/108\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}