How to select 1 date field with 3 tags in Oracle SQL?


I'm doing query in oracle 11g and they ask me to select the last 3 dates when a client paid invoice. The query I have it like this:

    t1.datefield date_1,
    t1.datefield date_2,
    t1.datefield date_3
    table t1
    t1.datefield BETWEEN TO_DATE('01/07/2016', 'dd/mm/yyyy') 
    AND TO_DATE('31/07/2016', 'dd/mm/yyyy');

I need that month_1 be from July 1 to July 31, month_2 from August 1 to August 31 and month_3 from September 1 to September 30 ...

How can I solve that doubt?

asked by GioV 28.10.2016 в 15:52

2 answers


What I understand is that you want the last 3 dates in a single row (I do not complicate the logic of the months, since it seems that it does not really matter). If so, this query should work. You do not need to joins:

select max(case when rn = 3 then datefield end) as mes_1,
       max(case when rn = 2 then datefield end) as mes_2,
       max(case when rn = 1 then datefield end) as mes_3
  from (select t.datefield,
               row_number() over (order by t.datefield desc) as rn
          from tbl t)
 where rn <= 3
answered by 28.10.2016 / 20:32

What you can do is use the row_number () function, in this case the list goes in rows

  SELECT datefield from(
   over(partition by TO_CHAR(TO_DATE(datefield,'dd/mm/yyyy'),'MM') order by datefield desc ) as rn
   from tabla1)
   where rn <=3)

If you need it in columns you can use the clause With :

WITH tabla AS
           TO_CHAR(TO_DATE(datefield,'dd/mm/yyyy'),'MM') AS idFecha,
           row_number() over(partition BY TO_CHAR(TO_DATE(datefield,'dd/mm/yyyy'),'MM')
                             ORDER BY datefield DESC) AS rn
   FROM tabla1)
SELECT t1.datefield fecha1,
       t2.datefield fecha2,
       t3.datefield fecha3
FROM tabla t1
inner join tabla t2 on (t2.idFecha>t1.idFecha and t2.rn=t1.rn)
inner join tabla t3 on (t3.idFecha>t2.idFecha and t3.rn=t1.rn)
WHERE /*t1.idFecha=7*/ /*fecha del mes*/
  t1.rn<=1; /*Considera este número como la cantidad de registros por mes que quieres sacar*/
answered by 28.10.2016 в 19:24