Q: query to obtain maximum 3 results I have a table with some fileds like these:
**Date           Name       Table      Direction      Code**
13/01/1978     Jacks      xxxx       stret ...      1
13/01/1978     John       xxxx       xxxxx          0
...........
12/01/1978     Dave       xxxx       xxxxxx         0
12/01/1978     Suse       xxxxx      xxxxxx         0
...........
11/01/1978     Mickey     xxxx       xxxxx          1
11/01/1978     Suse       xxxxxx     xxxxxxx        2
11/01/1978     Nune       xxxxxx     xxxxxxx        2
.....
09/01/1978     .....      ......     .......        1
08/01/1978     .....      ......     .......        0
21/01/1978     .....      ......     .......        1
.....

I would like to extract the three first ocurrences grouping by date descendent and detect if the field code have an value '1' in any row with the same date. The result that I would like is:
The first date is 21/01/1978, and have code 1
The second date is 13/01/1978 and have code 1
The third date is 12/01/1978 and don't have code 1

I'm trying with rownum but not works. Could you help me please? Thanks and sorry for my English!

A: I don't know why rownum didn't work for u, maybe becuase you tried to put in in the inner query before the order by(you first have to order and only then use rownum becuase every row will get another rownum)
SELECT Date,code from (
(SELECT Date,max(Code) as code from YourTable
GROUP BY Date
ORDER BY Date DESC
)
WHERE rownum <= 3


A: 
Try this Hope it helps

SELECT DISTINCT B.DT,
  DECODE(B.VL,1,' has code 1',999,' dont have code 1') inf
FROM
  (SELECT A.DT,
    CASE
      WHEN A.CODE = 1
      THEN 1
      ELSE 999
    END VL,
    ROW_NUMBER() OVER(PARTITION BY A.DT ORDER BY A.CODE DESC) RN
  FROM
    (SELECT '01/03/2016' AS DT,1 AS CODE FROM DUAL
    UNION ALL
    SELECT '01/03/2016' AS DT,0 AS CODE FROM DUAL
    UNION ALL
    SELECT '05/03/2016' AS DT,0 AS CODE FROM DUAL
    UNION ALL
    SELECT '05/03/2016' AS DT,0 AS CODE FROM DUAL
    UNION ALL
    SELECT '06/03/2016' AS DT,1 AS CODE FROM DUAL
    )A
  ) B
  WHERE B.RN = 1;


A: you can do it in quite a few ways, one of them would be like:
select date, 
       decode(date_cnt, 0, 'dont have code = 1', 'have code = 1) 
from (
  select t.date, 
         max(t.code) as date_cnt
     from table_name t
     group by date
     order by date desc)
where rownum <=3

this way you first sort them by date in the inner query aswell as prepare the info about having a code = 1. The outer query will fetch first 3 rows and change the codes to desired info (if  0 => no code, else there is code)
