Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unanswered: Comparing rows in table against themselves ??

    i have a problem in that i dont know how to Comparing rows in table against themselves. at the moments my code looks like :

    LOOP
    FETCH Topic_Result_ID Then Insert INTO Sel_Top(Topic_Result_ID = Topic_Result_ID);
    IF Topic_Result_ID =
    Topic_Result_ID%FOUND > Topic_Result_ID
    THEN
    Select Topic_Keyword_Counter
    From Top_Key
    Where Topic_Result_ID = Selected_Topic_ID
    Then Topic_Keyword_Counter ++;

    i am trying to find the Topic_Result_ID with the largest number of same values in the table but this is not working

    can anyone suggest a better way of doing it ??

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Comparing rows in table against themselves ??

    If your code really looks like that it won't even compile, let alone run

    Something like this will do it:
    PHP Code:
    select topic_result_id
    from
    select topic_result_idcount(*) cnt
      from the_table
      group by topic_result_id
      order by cnt desc
    )
    where ROWNUM 1

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Are you trying to find the count of occurrence of a Topic_Result_ID ?

    You can do this using

    Code:
    select Topic_Result_ID, count(*) topic_count from table
    group by Topic_Result_ID;
    If you want the row which has maximum topic_count value, you can do using

    Code:
    select Topic_Result_ID from
    (
    select Topic_Result_ID, count(*) topic_count from table
    group by Topic_Result_ID
    order by topic_count desc
    ) where rownum <2;
    If what you want to do is different from this, please explain what you want.
    Oracle can do wonders !

  4. #4
    Join Date
    Feb 2004
    Posts
    16
    yes i want to insert the largest in to another table called Sel_Top then increase the counter of the inserted line in Topic_Keyword_Counter in another table Key_Top is this all possible ??

    if so how, sorry to be a pain

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Simple SQL statements like this are all you need:
    PHP Code:
    insert into Sel_Toptopic_result_id values v_result_id );

    update Key_Top
    set counter 
    counter+
    where topic_result_id 
    v_result_id
    (v_result_id is a variable containing the value returned by the select statement we showed you earlier).

    It seems you don't really know much SQL or PL/SQL at all. It's hard to learn via a forum, you should get a book or study the manuals.

Posting Permissions

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