How to select specific rows in PostgreSQL query?

Question

I have written a query to get the 'reason_code' and 'app_count' for top five rows from result set and sum of remaining app_count under name 'others'.

Here what I have tried:

(SELECT a.app_pgm_rsnd_rsn_cd, a.denied_app_count
FROM (SELECT app_pgm_rsnd_rsn_cd ,COUNT(1) as denied_app_count
FROM app_pgm_choice, ead_case 
where app_pgm_sts= 'DN'
AND app_pgm_req_dt >= '20150101'
AND app_pgm_req_dt <= '20150130'
AND EAD_CS_APP_NUM =  APP_PGM_NUM
AND EAD_CS_SEND_CNTY_ID = '19'
AND EAD_CS_TRAN_STS = 'PE'
GROUP BY app_pgm_rsnd_rsn_cd
ORDER BY denied_app_count desc) a
WHERE ROWNUM <=5 )

UNION ALL

(SELECT 'OTHERS' as app_pgm_rsnd_rsn_cd, SUM(b.denied_app_count) as    denied_app_count
FROM (SELECT app_pgm_rsnd_rsn_cd ,COUNT(1) as denied_app_count
FROM app_pgm_choice, ead_case 
where app_pgm_sts= 'DN'
AND app_pgm_req_dt >= '20150101'
AND app_pgm_req_dt <= '20150130'
AND EAD_CS_APP_NUM =  APP_PGM_NUM
AND EAD_CS_SEND_CNTY_ID = '19'
AND EAD_CS_TRAN_STS = 'PE'
GROUP BY app_pgm_rsnd_rsn_cd
ORDER BY denied_app_count desc) b
WHERE ROWNUM >=5 )

But when I run this query it shows following error message:

ERROR: column "rownum" does not exist LINE 13: WHERE ROWNUM <=5 ) ^

********** Error **********

ERROR: column "rownum" does not exist SQL state: 42703 Character: 397

What is option for ROWNUM variable?

Answer 1

I think you are looking for the LIMIT clause.

SELECT * 
FROM sometable 
ORDER BY denied_app_count DESC
LIMIT 5

ROWNUM is an Oracle-ism.

For greater-than, you can use OFFSET:

SELECT * 
FROM sometable 
ORDER BY denied_app_count DESC
OFFSET 5

to skip the first 5 rows and return the rest.

The two can be, and often are, combined when doing things like pagination.

See LIMIT and OFFSET

In PostgreSQL you can use the row_number() window function, but for your purpose here it's unnecessary. On the other hand, it might be faster to do a single query with row_number then query the result table twice to get the two parts. Try that approach and see if it performs better.