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?

Craig Ringer · Accepted Answer · 2015-07-06 06:59:25Z

7

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.

edited Jul 6, 2015 at 6:59

answered Jul 6, 2015 at 6:44

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Madhusudan Over a year ago

But how can I select LIMIT for selecting rows with rownum >5 ?

Madhusudan Over a year ago

Yeah. This is amazing. It's not even making my query complex. Looks Good Now. Thanks :)

Mukesh Kalgude · Accepted Answer · 2015-07-06 06:34:23Z

1

Try this query

(SELECT a.app_pgm_rsnd_rsn_cd, a.denied_app_count
FROM (SELECT app_pgm_rsnd_rsn_cd ,COUNT(1) as denied_app_count
,row_number() OVER (ORDER BY app_pgm_rsnd_rsn_cd ) AS ROWNUM 
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
,row_number() OVER (ORDER BY app_pgm_rsnd_rsn_cd ) AS ROWNUM 
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 )

edited Jul 6, 2015 at 6:34

answered Jul 6, 2015 at 6:21

Mukesh Kalgude

4,8542 gold badges21 silver badges32 bronze badges

4 Comments

Madhusudan Over a year ago

Its showing this error: ERROR: column "denied_app_count" does not exist

Madhusudan Over a year ago

Super. This works. Thanks :). Can you please add some description to your answer. What you did and all?

Mukesh Kalgude Over a year ago

If it is help out then given right mark and also given vote

Craig Ringer Over a year ago

@MukeshKalgude It's really a good idea to explain why where possible. (I don't upvote code-only answers, btw).

Collectives™ on Stack Overflow

How to select specific rows in PostgreSQL query?

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related