How to select data in postgres with count and limit in a single query

Question

A PostgreSQL table with data in the format

the table name is tbl1 tbl1

id -- RCODE -- CCODE -- LDATA
1     123      50        p1
2     124      51        p2
3     126      50        p3

....................... ......... ..... .

23     116      56        p3
24     126      50        p9
25     126      50        p3
26     136      56        p5
27     126      50        p3
28     146      52        p7

My problem is how to find the count of CCODE =50 from last 7 records of the db having RCODE =126

Joseph Victor Zammit · Accepted Answer · 2012-10-08 12:15:29Z

1

Use a subquery to generate an intermediate table a which contains the last 7 records of the db having RCODE=126. Then run COUNT over it WHERE CCODE=50. Query:

SELECT COUNT(*)
FROM (
    SELECT CCODE
    FROM tbl1
    WHERE RCODE = 126
    ORDER BY id DESC LIMIT 7
) AS a
WHERE CCODE = 50

edited Oct 8, 2012 at 12:15

answered Oct 8, 2012 at 12:10

Joseph Victor Zammit

15.4k13 gold badges81 silver badges104 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2012-10-08 12:08:37Z

0

select count(*) as total
from (
    select CCODE
    from tbl1
    where RCODE = 126
    order by id desc
    limit 7
    ) s
where CCODE = 50

answered Oct 8, 2012 at 12:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

How to select data in postgres with count and limit in a single query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related