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

Navigation
1

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: 

    SELECT 
    t1.datefield date_1,
    t1.datefield date_2,
    t1.datefield date_3
FROM
    table t1
WHERE
    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
source

2 answers

2

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
source
0

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

  SELECT datefield from(
   SELECT 
   datefield,
   row_number() 
   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
  ( SELECT 
           TO_CHAR(TO_DATE(datefield,'dd/mm/yyyy'),'MM') AS idFecha,
           datefield,
           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
No static method getDefaultAPIVersion when calling FacebookSdk.sdkInitialize Modify a variable from the server with Socket.io