up vote 1 down vote favorite

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

share|improve this question
20% accept rate
feedback

2 Answers

up vote 1 down vote

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
share|improve this answer
feedback
up vote 0 down vote 
select count(*) as total
from (
    select CCODE
    from tbl1
    where RCODE = 126
    order by id desc
    limit 7
    ) s
where CCODE = 50
share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.