Results 1 to 3 of 3

Thread: Query

  1. #1
    Join Date
    Jul 2002
    Posts
    19

    Unanswered: Query

    I have a requirement like this
    There are about 1.8 million records in a single table.
    Consider i have 5 columns Col1,2.....5
    I need to sort these columns based on a particular order
    say col 1-4 ascending and 5th column descending (Say they are in groups of 15-25 records each)
    After this sort i need to fetch the value of col5 in the First record and update the col5 for the rest of the records IN THAT GROUP.
    Can someone provide an optimized way of doing this?
    Thanks in advance
    Regards
    C.Gajendran

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Do you not mean GROUP the data by the first 4 columns and update to the MIN of the 5th column?

    update yourtable
    set col5 =
    (
    select col5
    from
    (
    select col1,col2,col3,col4,min(col5) col5
    from yourtable
    group by col1, col2, col3, col4
    ) t
    where yourtable.col1 = t.col1
    and yourtable.col2 = t.col2
    and yourtable.col3 = t.col3
    and yourtable.col4 = t.col4
    );

    Apologies if I have misunderstood.

  3. #3
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    Pls clarify

    how these records are group, example having equal value in col1 (col2..col4), having a range/list values for col1 (col2...col4).

    Thanks !
    Oliver

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •