Forum Stats

  • 3,853,280 Users
  • 2,264,201 Discussions
  • 7,905,303 Comments

Discussions

Complex select query help (Group by)

2

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    user12050217 wrote:
    I would like to thanks to all for your fast and valuable replies. Here issue is how to order by; so i am assuming that order by rownum can be work; because once entered data, it will never updated and as we insert the rows, i hope it will be inserted at the bottom (free space in the extent).
    As was mentioned previously there is no predefined order. There is no default that says data is returned in the order it was inserted. If there was this could make index access painfully slow.

    I believe you are going to have to modify the table structure.
  • user12050217
    user12050217 Member Posts: 927
    SQL> create table newbus as select rownum id,bussno,start_date,passengers from bustour;
    
    Table created.
    
    SQL> select * from newbus;
    
            ID     BUSSNO START_DATE PASSENGERS
    ---------- ---------- ---------- ----------
             1       4123 04-10-2009         20
             2       4123 05-10-2009         25
             3       4123 05-10-2009         18
             4       4123 06-10-2009         15
             5       6138 03-10-2009         16
             6       6138 03-10-2009         19
             7       6138 04-10-2009         22
             8       6138 04-10-2009         13
             9       4123 07-10-2009         23
            10       4123 08-10-2009         27
            11       4123 11-10-2009         15
    
            ID     BUSSNO START_DATE PASSENGERS
    ---------- ---------- ---------- ----------
            12       6138 05-10-2009         16
            13       6138 05-10-2009         13
            14       6138 05-10-2009         18
            15       6138 05-10-2009         24
            16       6138 07-10-2009         20
            17       6138 08-10-2009         18
    
    17 rows selected.
    
    SQL> select * from newbus order by id;
    
            ID     BUSSNO START_DATE PASSENGERS
    ---------- ---------- ---------- ----------
             1       4123 04-10-2009         20
             2       4123 05-10-2009         25
             3       4123 05-10-2009         18
             4       4123 06-10-2009         15
             5       6138 03-10-2009         16
             6       6138 03-10-2009         19
             7       6138 04-10-2009         22
             8       6138 04-10-2009         13
             9       4123 07-10-2009         23
            10       4123 08-10-2009         27
            11       4123 11-10-2009         15
    
            ID     BUSSNO START_DATE PASSENGERS
    ---------- ---------- ---------- ----------
            12       6138 05-10-2009         16
            13       6138 05-10-2009         13
            14       6138 05-10-2009         18
            15       6138 05-10-2009         24
            16       6138 07-10-2009         20
            17       6138 08-10-2009         18
    
    17 rows selected.
    
    SQL>
    Now i think i have id column for order by clause. We will update our application to insert the id value max(id)+1.
    Regards
  • kendenny
    kendenny Member Posts: 1,269
    Don't use max(id)+1, use a sequence. If you use max(id)+1 and two users enter data at the same time you'll get duplicate id's.
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    user12050217 wrote:
    Now i think i have id column for order by clause. We will update our application to insert the id value max(id)+1.
    As well as not using a sequence to generate your id's, relying on id's for ordering can produce incorrect results, as rows can get an id, and then wait for the user to finish their lunch before they click on the "submit" button. If you're interested in the time at which the row was inserted, then, as I've suggested before, use a timestamp column, along with a trigger to update the time to systimestamp.

    That way, you'll always get the time at which the row was added into the table.

    I still fail to see why your managers require differing time ranges per bus number to work out bonuses; surely a consistent range (a week/4 days/a month/etc) would be better? Have you queried that with them?
  • user12050217
    user12050217 Member Posts: 927
    Our management has decided to give bonus to drivers, on running the bus on its full sitting capacity. So, when bus's trip starts and go to end; for those no. of days (which are not fixed and certain) a certain % of bonus will be calculated as per my query output;including value of totalpassengers. As far as application and sequence concern, only one data entry operator runs the application and enters the data in the table. If still it require to use sequence; i shall keep in mind when updation in application done.

    Regards
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    user12050217 wrote:
    As far as application and sequence concern, only one data entry operator runs the application and enters the data in the table.
    Maybe... for now. What happens if they get someone else in to help out?

    And will the operator(s) know that the order in which they key the data is important? What determines the order of how they key in the data?
    If still it require to use sequence; i shall keep in mind when updation in application done.
    No. If you're going to go with ID's, USE A SEQUENCE to generate them!! Trying to work out the max id and then add 1 to it is only going to get slower the more rows you have in the table. It's a bad idea to re-invent the wheel!
  • user12050217
    user12050217 Member Posts: 927
    Sir,
    Earlier it was issue that table do'nt have order by column, so i added it. Now, i am not able to understand the use of sequence / id column to create the required output please. Here id column is the record number and its another issue, when we update the application. Now, table is having order by column, so please guide me how to write the select query on the existing table. Rest will be sorted out in due course please.

    I am not getting your mean by "No. If you're going to go with ID's, USE A SEQUENCE to generate them!! "
    Kindly elaborate more.
    Thank you sir.
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    You create a sequence to auto generate the ids for you.

    Then you insert them into the table like:
    insert into table_name (id_column, ....) values (sequence_name.nextval, .....);
    That way, you don't have to worry about working out what the next id you should pick; it's already done for you.

    You could also have a trigger to do the insert if no id is specified.
    Boneist
  • Ok, with this i will do the required changes in the application its sequence / id issue. But i am waiting for select query please.

    Regards
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited Oct 13, 2009 10:53AM Answer ✓
    Ok, something like this:
    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)
    -- end of mimicking your data; use SQL below:
    select busno,
           min(start_date) start_date,
           max(start_date) end_date,
           sum(passengers) total_passengers,
           max(passengers) max_passengers,
           max_pass_date
    from   (select id,
                   start_date,
                   busno,
                   passengers,
                   distance,
                   max(start_date) keep (dense_rank first order by passengers desc) over (partition by distance, busno) max_pass_date
            from   (select id,
                           start_date,
                           busno,
                           passengers,
                           -- using tabibitosan method, borrowed from Aketi Jyuuzou 
                           dense_rank() over (order by id) -
                              row_number() over (partition by busno order by id) distance
                    from   my_tab))
    group by busno, distance, max_pass_date
    order by min(id)
    
         BUSNO START_DATE END_DATE   TOTAL_PASSENGERS MAX_PASSENGERS MAX_PASS_DATE
    ---------- ---------- ---------- ---------------- -------------- -------------
          4123 04/10/2009 06/10/2009               78             25 05/10/2009   
          6138 03/10/2009 04/10/2009               70             22 04/10/2009   
          4123 07/10/2009 11/10/2009               65             27 08/10/2009   
          6138 05/10/2009 08/10/2009              109             24 05/10/2009   
This discussion has been closed.