9 Replies Latest reply: May 27, 2011 8:56 AM by Aketi Jyuuzou RSS

    sql query

    user8792751
      Hi,

      i have a table like this
      daydate          col1     col2   col3  col4  col5
      2004-05-20     1     10     100     33     11
      2004-05-21     1     10     100     33     11
      2004-05-22     1     13     100     44     11
      2004-05-23     4     13     120     44     12
      2004-05-24     4     56     120     55     12
      2004-05-25     4     56     130     55     12
      2004-05-26     4     56     130     66     12
      2004-05-27     7     76     150     66     15
      2004-05-28     7     76     150     77     15
      2004-05-29     7     77     170     77     15
      2004-05-30     7     77     180     77     17
      This is my structure of the table.

      i need to retrieve the numbers and the corresponding dates which occurs first for every column.

      My out put should be like this
      value     date
      1         2004-05-20
      4         2004-05-23
      7         2004-05-27
      10       2004-05-20
      13       2004-05-22
      56       2004-05-24
      76       2004-05-27
      etc
      here the vaule numbers should go from col1 to col5

      Edited by: user8792751 on May 27, 2011 5:19 AM
        • 1. Re: sql query
          GokhanAtil
          user8792751 wrote:
          Hi,

          i have a table like this
          daydate 
          2004-05-20     1     10     100     33     11
          2004-05-21     1     10     100     33     11
          2004-05-22     1     13     100     44     11
          2004-05-23     4     13     120     44     12
          2004-05-24     4     56     120     55     12
          2004-05-25     4     56     130     55     12
          2004-05-26     4     56     130     66     12
          2004-05-27     7     76     150     66     15
          2004-05-28     7     76     150     77     15
          2004-05-29     7     77     170     77     15
          2004-05-30     7     77     180     77     17
          and?
          • 2. Re: sql query
            Peter Gjelstrup
            user8792751 wrote:
            Hi,

            i have a table like this
            No, that is not a table. That is just text arranged in columns.

            Better to post create table + insert statements if you want us to work with your data.


            Regards
            Peter
            • 3. Re: sql query
              Arun Kumar Gupta
              Need this
              SELECT col1       value
                    ,daydate    date1
                FROM ( SELECT col1
                             ,daydate
                             ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                         FROM table_name
                     )
               WHERE  row_num = 1      
              UNION ALL
              SELECT col2       value
                    ,daydate    date1
                FROM ( SELECT col2
                             ,daydate
                             ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                         FROM table_name
                     )
               WHERE  row_num = 1
               UNION ALL
               ........Same for FOR col3............
               UNION ALL
               ........Same for FOR col4............
               
               UNION ALL
               SELECT col5       value
                     ,daydate    date1
                 FROM ( SELECT col5
                              ,daydate
                              ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                          FROM table_name
                      )
                WHERE  row_num = 1
              Assuming that the number of columns (col1 col2...) are fixed. More the columns more UNION ALL

              Regards
              Arun
              • 4. Re: sql query
                GokhanAtil
                What's your oracle version? and it could be a time-saver if you would share your CREATE/INSERT script for this table.
                • 5. Re: sql query
                  Martin Preiss
                  perhaps:
                  select col
                       , min(daydate)
                    from (select daydate
                               , col1 col
                            from t
                           union all
                          select daydate
                               , col2 col
                            from t
                           union all
                          select daydate
                               , col3 col
                            from t
                           union all         
                          select daydate
                               , col4 col
                            from t
                           union all    
                          select daydate
                               , col5 col
                            from t) 
                   group by col  
                   order by col                    
                  Regards

                  Martin Preiss

                  Edited by: mpreiss on May 27, 2011 2:39 PM
                  • 6. Re: sql query
                    user8792751
                    table creation
                    CREATE TABLE test(daydate date,col1 int,col2 int,col3 int ,col4 int,col5 int);
                    for inserts
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-20','YYYY-MM-DD'), 1, 10, 100, 33, 11);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-21','YYYY-MM-DD'), 1, 10, 100, 33, 11);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-22','YYYY-MM-DD'), 1, 13, 100, 44, 11);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-23','YYYY-MM-DD'), 4, 13, 120, 44, 12);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-24','YYYY-MM-DD'), 4, 56, 120, 55, 12);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-25','YYYY-MM-DD'), 4, 56, 130, 55, 12);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-26','YYYY-MM-DD'), 4, 56, 130, 66, 12);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-27','YYYY-MM-DD'), 7, 76, 150, 66, 15);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-28','YYYY-MM-DD'), 7, 76, 150, 77, 15);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-29','YYYY-MM-DD'), 7, 77, 170, 77, 15);
                    INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-30','YYYY-MM-DD'), 7, 77, 180, 77, 17);
                    
                    commit;
                    Edited by: user8792751 on May 27, 2011 5:36 AM
                    Comment: sorry forgot to update the date

                    Edited by: user8792751 on May 27, 2011 5:46 AM
                    • 7. Re: sql query
                      user8792751
                      Hi Arun,
                      Thanks for ur info,
                      i dint get the desired output with this query..
                       1  SELECT col1       value
                       2        ,daydate    date1
                       3    FROM ( SELECT col1
                       4                 ,daydate
                       5                 ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                       6             FROM test
                       7         )
                       8   WHERE  row_num = 1
                       9  UNION ALL
                      10  SELECT col2       value
                      11        ,daydate    date1
                      12    FROM ( SELECT col2
                      13                 ,daydate
                      14                 ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                      15             FROM test
                      16         )
                      17   WHERE  row_num = 1
                      18   UNION ALL
                      19   SELECT col3       value
                      20         ,daydate    date1
                      21     FROM ( SELECT col3
                      22                  ,daydate
                      23                  ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                      24              FROM test
                      25          )
                      26    WHERE  row_num = 1
                      27   UNION ALL
                      28   SELECT col4       value
                      29         ,daydate    date1
                      30     FROM ( SELECT col4
                      31                  ,daydate
                      32                  ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                      33              FROM test
                      34          )
                      35    WHERE  row_num = 1
                      36   UNION ALL
                      37   SELECT col5       value
                      38         ,daydate    date1
                      39     FROM ( SELECT col5
                      40                  ,daydate
                      41                  ,ROW_NUMBER() OVER (ORDER BY daydate)  row_num
                      42              FROM test
                      43          )
                      44*   WHERE  row_num = 1
                      QL> /
                      
                          VALUE DATE1
                      --------- ------------------
                              1 20-MAY-04
                             10 20-MAY-04
                            100 20-MAY-04
                             33 20-MAY-04
                             11 20-MAY-04
                      • 8. Re: sql query
                        Mahir M. Quluzade
                        Hi ,

                        try this please
                        CREATE TABLE test(daydate date,col1 int,col2 int,col3 int ,col4 int,col5 int);
                        
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-20','YYYY-MM-DD'), 1, 10, 100, 33, 11);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-21','YYYY-MM-DD'), 1, 10, 100, 33, 11);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-22','YYYY-MM-DD'), 1, 13, 100, 44, 11);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-23','YYYY-MM-DD'), 4, 13, 120, 44, 12);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-24','YYYY-MM-DD'), 4, 56, 120, 55, 12);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-25','YYYY-MM-DD'), 4, 56, 130, 55, 12);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-26','YYYY-MM-DD'), 4, 56, 130, 66, 12);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-27','YYYY-MM-DD'), 7, 76, 150, 66, 15);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-28','YYYY-MM-DD'), 7, 76, 150, 77, 15);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-29','YYYY-MM-DD'), 7, 77, 170, 77, 15);
                        INSERT INTO TEST (daydate, col1, col2, col3, col4, col5) VALUES (TO_DATE('2004-05-30','YYYY-MM-DD'), 7, 77, 180, 77, 17);
                         
                        commit;
                        
                        
                        WITH DATA AS 
                        (SELECT col1 AS col, min(daydate) date_min
                        FROM TEST
                        GROUP BY col1
                        UNION ALL
                        SELECT col2 , min(daydate) date_min
                        FROM TEST
                        GROUP BY col2
                        UNION ALL
                        SELECT col3 , min(daydate) date_min
                        FROM TEST
                        GROUP BY col3
                        UNION ALL
                        SELECT col4 , min(daydate) date_min
                        FROM TEST
                        GROUP BY col4
                        union all
                        SELECT col5 , min(daydate) date_min
                        FROM TEST
                        GROUP BY col5)
                        
                        select * from data order by col;
                        
                        
                        
                        select * from test;
                        Edited by: Mahir M. Quluzade on May 27, 2011 6:01 PM
                        • 9. Re: sql query
                          Aketi Jyuuzou
                          UnPivot is useful :-)
                          with t(daydate,col1,col2) as(
                          select date '2004-05-20',1,10 from dual union
                          select date '2004-05-21',1,10 from dual union
                          select date '2004-05-22',1,13 from dual union
                          select date '2004-05-23',4,13 from dual union
                          select date '2004-05-24',4,56 from dual union
                          select date '2004-05-25',4,56 from dual union
                          select date '2004-05-26',4,56 from dual union
                          select date '2004-05-27',7,76 from dual union
                          select date '2004-05-28',7,76 from dual union
                          select date '2004-05-29',7,77 from dual union
                          select date '2004-05-30',7,77 from dual)
                          select Vals,min(daydate)
                            from t UnPivot(Vals for sortKeys
                                           in (col1 as 1,
                                               col2 as 2))
                          group by Vals
                          order by min(sortKeys),Vals;
                          
                          VALS  MIN(DAYD
                          ----  --------
                             1  04-05-20
                             4  04-05-23
                             7  04-05-27
                            10  04-05-20
                            13  04-05-22
                            56  04-05-24
                            76  04-05-27
                            77  04-05-29