CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1psql
cc4re
cc4re

Reputation: 4901

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

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

Upvotes: 1

Views: 2900

Answers (2)

Joseph Victor Zammit
Joseph Victor Zammit

Reputation: 15320

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

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125564

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

Upvotes: 0

Related Questions