This discussion is archived
9 Replies Latest reply: May 27, 2011 6:56 AM by Aketi Jyuuzou RSS

sql query

user8792751 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    ArunKumarGupta Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points