Complex select query help (Group by)

Centinul · Oct 13, 2009 8:29AM

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 · Oct 13, 2009 8:36AM

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 · Oct 13, 2009 8:40AM

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 · Oct 13, 2009 8:49AM

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 · Oct 13, 2009 8:59AM

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 · Oct 13, 2009 9:09AM

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 · Oct 13, 2009 9:20AM

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 · Oct 13, 2009 9:45AM

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.

user12050217 · Oct 13, 2009 10:23AM

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 · Oct 13, 2009 10:52AM

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

Forum Stats

Quick Links

Categories

Complex select query help (Group by)

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Complex select query help (Group by)

Answers