10 Replies Latest reply: Sep 25, 2013 1:22 PM by AshwinWar RSS

    SQL HELP

    AshwinWar

      Dear Friends,

       

      select EXCHNG, count(*), min(WO_DT) from tblwo group by exchng;

      EXCH   COUNT(*)   MIN(WO_DT

       

      FTN          17    07-JAN-08

      KLB           3    05-APR-08

      CFP           3    04-FEB-09

      CTY          13    19-FEB-08

      COOP         17 12-MAR-08

       

      Desired Output is

       

      EXCH   COUNT(*)  MIN(WO_DT)  2ndlowest_dt    3rd Lowestdt

      FTN          17    07-JAN-08     10-JAN-08               12-Jan-08

       

       

      Please help thanks in advance.

        • 1. Re: SQL HELP
          Hoek

          No database version.

          No create table/insert into statements or a data example using the with-clause...

          Time to read:

          Re: 2. How do I ask a question on the forums?

          • 2. Re: SQL HELP
            AshwinWar

            insert into tblwo ('FTN',07-JAN-01);

            insert into tblwo ('FTN',08-JAN-01);

            insert into tblwo ('FTN',09-JAN-01);

            insert into tblwo ('FTN',10-JAN-01);

            insert into tblwo ('KLB',07-JAN-01);

            insert into tblwo ('KLB',08-JAN-01);

            insert into tblwo ('KLB',09-JAN-01);

            insert into tblwo ('KLB',10-JAN-01);

            • 3. Re: SQL HELP
              Frank Kulash

              Hi,

               

              Thanks for posting the INSERT statements.

              Don't forget to post the CREATE TABLE statement, the output you want from that sample data (I'm assuming the correct output from that data does not include date from 2008, since all the data now is from 2001), an explanation of how you get those results from that data, and your Oracle version.

              Did you really read the forum FAQ?  https://forums.oracle.com/message/9362002

              • 4. Re: SQL HELP
                SomeoneElse

                Really?

                 

                SQL> insert into tblwo ('FTN',07-JAN-01);
                insert into tblwo ('FTN',07-JAN-01)
                                   *
                ERROR at line 1:
                ORA-00928: missing SELECT keyword


                SQL> insert into tblwo ('FTN',08-JAN-01);
                insert into tblwo ('FTN',08-JAN-01)
                                   *
                ERROR at line 1:
                ORA-00928: missing SELECT keyword


                SQL> insert into tblwo ('FTN',09-JAN-01);
                insert into tblwo ('FTN',09-JAN-01)
                                   *
                ERROR at line 1:
                ORA-00928: missing SELECT keyword

                ...

                ...

                • 5. Re: SQL HELP
                  AshwinWar

                  Extremely sorry friends: please find the correct data......

                   

                  create table tblwo (exchng  varchar2(12), wo_dt date);

                   

                   

                   

                   

                  insert into tblwo values ('FTN','07-JAN-01');

                  insert into tblwo values ('FTN','08-JAN-01');

                  insert into tblwo values ('FTN','09-JAN-01');

                  insert into tblwo values ('FTN','10-JAN-01');

                  insert into tblwo values ('KLB','07-JAN-01');

                  insert into tblwo values ('KLB','08-JAN-01');

                  insert into tblwo values ('KLB','09-JAN-01');

                   

                   

                  Desired Output is

                   

                  EXCH     COUNT(*)  MIN(WO_DT)  2ndlowest_dt    3rd Lowestdt

                  FTN          4    07-JAN-01     08-JAN-08      09-Jan-01

                  KLB          3    07-JAN-01     08-JAN-08      09-Jan-01

                  • 6. Re: SQL HELP
                    SomeoneElse

                    > insert into tblwo values ('FTN','07-JAN-01');

                     

                    2 digit years are kind of passe.

                     

                    Is this January 7th of 2001 or January 1st of 2007?

                    • 7. Re: SQL HELP
                      AshwinWar

                      create table tblwo (exchng  varchar2(12), wo_dt date);

                       

                       

                       

                       

                      insert into tblwo values ('FTN','07-JAN-2001');

                      insert into tblwo values ('FTN','08-JAN-2001');

                      insert into tblwo values ('FTN','09-JAN-2001');

                      insert into tblwo values ('FTN','10-JAN-2001');

                      insert into tblwo values ('KLB','07-JAN-2001');

                      insert into tblwo values ('KLB','08-JAN-2001');

                      insert into tblwo values ('KLB','09-JAN-2001');

                       

                       

                      Desired Output is

                       

                      EXCH     COUNT(*)  MIN(WO_DT)  2ndlowest_dt    3rd Lowestdt

                      FTN          4    07-JAN-2001     08-JAN-2001      09-Jan-2001

                      KLB          3    07-JAN-2001     08-JAN-2001      09-Jan-2001

                       

                      • 8. Re: SQL HELP
                        SomeoneElse

                        SQL> select exchng
                          2        ,count(*)
                          3        ,min(decode(dr,1,wo_dt,null)) lowest
                          4        ,min(decode(dr,2,wo_dt,null)) next_lowest
                          5        ,min(decode(dr,3,wo_dt,null)) third_lowest
                          6  from
                          7  (
                          8     select t.*
                          9           ,dense_rank() over (partition by exchng order by wo_dt) dr
                        10     from   tblwo t
                        11  )
                        12  group by exchng
                        13  order by exchng
                        14  ;

                        EXCHNG                   COUNT(*) LOWEST               NEXT_LOWEST          THIRD_LOWEST
                        ------------ -------------------- -------------------- -------------------- --------------------
                        FTN                             4 07-JAN-2001 00:00:00 08-JAN-2001 00:00:00 09-JAN-2001 00:00:00
                        KLB                             3 07-JAN-2001 00:00:00 08-JAN-2001 00:00:00 09-JAN-2001 00:00:00

                        • 9. Re: SQL HELP
                          Frank Kulash

                          Hi,

                           

                          What you want is called a Pivot.  The best way to do it depends on your Oracle version, which illustrates why you always need to say which version of Oracle you're using.

                          In Oracle 9.1 and up, you can do this:

                          WITH got_r_num AS

                          (

                              SELECT  exchng, wo_dt

                              ,       ROW_NUMBER () OVER ( PARTITION BY  exchng

                                                           ORDER BY      wo_dt

                                                         )  AS r_num

                              FROM    tblwo

                          )

                          SELECT    exchng

                          ,         COUNT (*)                                 AS cnt

                          ,         MIN (CASE WHEN r_num = 1 THEN wo_dt END)  AS wo_dt_1

                          ,         MIN (CASE WHEN r_num = 2 THEN wo_dt END)  AS wo_dt_2

                          ,         MIN (CASE WHEN r_num = 3 THEN wo_dt END)  AS wo_dt_3

                          FROM      got_r_num

                          GROUP BY  exchng

                          ORDER BY  exchng

                          ;

                          Starting in Oracle 11, you can also use the SELECT ... PIVOT feature, though, for this particular problem, the technique used above might be simpler.

                           

                          Don't try to insert a VARCHAR2 (such as '07-JAN-01') into a DATE column.  Use a DATE instead.  As Someoneelse mentioned, using 20digit years is just asking fro trouble; always use 4-digit years, like this:

                           

                          INSERT INTO tblwo (exchng, wo_dt)

                              VALUES        ('FTN',  TO_DATE ( '07-JAN-2001'

                                                             , 'DD-MON-YYYY'

                                                             )

                                            );

                          • 10. Re: SQL HELP
                            AshwinWar

                            thanks someoneElse and Frank.