1

I need to query the top 5 value from tableA. Such as below

    select id, count(occurrence), date 
    from 
    (select id, unnest(value) as occurrence, date from tableA) as a
    group by id, occurrence, date 
    order by occurrence desc 
    limit 5

 id  | occurrence |   date   
-----+-------+----------
 330 |    11 | 20141015
 400 |    11 | 20141015
 390 |    10 | 20141015
 240 |    10 | 20141015
 501 |    10 | 20141015

and after received the ids, query the same tableA to get all the values of the id for other dates ranges from 20140101 until 20141015. 

expected result:

 id  | occurrence |   date   
-----+-------+----------
 330 |    11 | 20141015
 400 |    11 | 20141015
 390 |    10 | 20141015
 240 |    10 | 20141015
 501 |    10 | 20141015
 330 |    0  | 20141014
 400 |    1  | 20141014
 390 |    10 | 20141014
 240 |    15 | 20141014
 501 |    10 | 20141014
 330 |    11 | 20141013
 400 |    11 | 20141013
 390 |    11 | 20141013
 240 |    19 | 20141013
 501 |    10 | 20141013

But how exactly can I do that?

My postgresql version is 8.1 and I can't use partition(if any of u want to suggest that)

edit

select  id, count(value) as occurrence, date
from tableA
where id = ANY(
    select array(
        select id
        from (
            select date, unnest(id) as id
            from tableA where date>='20140101' and date<='20141015'
            )as a
        )
)
group by id, date

doesn't return anything. is my array correct?

momokjaaaaa
  • 1,293
  • 3
  • 17
  • 32

2 Answers2

0

You may try select sth from table where dt > 20140101 and dt < 20141015 and row_num <= 5 this will give you the right answer.

burak buruk
  • 111
  • 1
  • 5
0
select id, count(value) as occurrence, date
from t
where id in (
    select id
    from (
        select id, count(value) as occurrence, date 
        from t 
        group by id, date 
        order by occurrence desc 
        limit 5
    ) s
) q
group by id, date
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260