Forum Stats

  • 3,851,709 Users
  • 2,264,014 Discussions
  • 7,904,821 Comments

Discussions

Complex select query help (Group by)

13»

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Oct 13, 2009 11:12AM
    This is what I came up with, I'm not sure how full proof it is to be perfectly honest.

    Removed for a couple of reasons:

    1. The previously presented solution was better (nice solution Boneist) :)
    2. My max passenger date logic was incorrect.

    Edited by: Centinul on Oct 13, 2009 11:10 AM

    Boneist is there a link on the `Net for this: tabibitosan method, borrowed from Aketi Jyuuzou
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited Oct 13, 2009 11:41AM
    Sure; you don't think I {noformat}*remembered*{noformat} how to do it, do you?! *{;-)

    Here's where I first came across it: 3320506
    And here's another one: 3677639

    It was the 2nd link that I, um, "borrowed" from! ("copied" doesn't sound quite so nice... *{;-) )


    ETA: In fact, I only really "got" what it does by printing out the rownum and the dense_rank values to work out why one minus the other gives a consistent result; I can has teh dumb *{;-)

    Edited by: Boneist on 13-Oct-2009 16:40
    Boneist
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Boneist wrote:
    ETA: In fact, I only really "got" what it does by printing out the rownum and the dense_rank values to work out why one minus the other gives a consistent result; I can has teh dumb *{;-)
    That's the first thing I did when I was looking at your method :)

    Thanks for the links!
  • Thank you sir Boneist. Your query is giving me the exact output which i want.

    Regards
    Nisha Sharma
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I am glad that "sense of tabibitosan" is used ;-)
    with my_tab as (
    select 1 id, 4123 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
    select 2 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 25 passengers from dual union all
    select 3 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
    select 4 id, 4123 busno, to_date('06/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
    select 5 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
    select 6 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 19 passengers from dual union all
    select 7 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 22 passengers from dual union all
    select 8 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
    select 9 id, 4123 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 23 passengers from dual union all
    select 10 id, 4123 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 27 passengers from dual union all
    select 11 id, 4123 busno, to_date('11/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
    select 12 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
    select 13 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
    select 14 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
    select 15 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 24 passengers from dual union all
    select 16 id, 6138 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
    select 17 id, 6138 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual)
    select busno,min(start_date),max(start_date),sum(passengers),max(passengers),
    max(start_date) Keep(Dense_Rank Last order by passengers) as max_pass_date
      from (select ID,busno,start_date,passengers,
             Row_Number() over(order by id)
            -Row_Number() over(partition by busno order by id) as distance
              from my_tab)
    group by busno,distance
    order by min(id);
    
    BUSNO  MIN(STAR  MAX(STAR  SUM(PASSENGERS)  MAX(PASSENGERS)  MAX_PASS
    -----  --------  --------  ---------------  ---------------  --------
     4123  09-10-04  09-10-06               78               25  09-10-05
     6138  09-10-03  09-10-04               70               22  09-10-04
     4123  09-10-07  09-10-11               65               27  09-10-08
     6138  09-10-05  09-10-08              109               24  09-10-05
This discussion has been closed.