10 Replies Latest reply: Jan 4, 2013 9:32 PM by user503635 RSS

    select top 1 record from resultset (PL/SQL, oracle)

    809036
      i would like to ask how can i select top 1 record from the selected resultset to and then output as list in pl/sql
      it is because i always retrieve two or more record from the resultset. my sql like belows:

      select id from customer where surname ='WONG';

      it returns:

      2
      3
      4

      in fact the table contain id 1,2,3,4

      i have tried select id from customer where surname='WONG' and rownum=1;
      it always return id=1, how can i select top 1 from resultset , in this case, id=2
        • 1. Re: select top 1 record from resultset (PL/SQL, oracle)
          user503635
          Use open cursor
          fetch the first record, then exit.
          • 2. Re: select top 1 record from resultset (PL/SQL, oracle)
            BluShadow
            806033 wrote:
            i would like to ask how can i select top 1 record from the selected resultset to and then output as list in pl/sql
            it is because i always retrieve two or more record from the resultset. my sql like belows:

            select id from customer where surname ='WONG';

            it returns:

            2
            3
            4

            in fact the table contain id 1,2,3,4

            i have tried select id from customer where surname='WONG' and rownum=1;
            it always return id=1,
            how can it return id = 1 when there is no id=1 for the surname = 'WONG'?
            how can i select top 1 from resultset , in this case, id=2
            how about
            select min(id) from customer where surname = 'WONG';
            • 3. Re: select top 1 record from resultset (PL/SQL, oracle)
              809036
              it just return top 1 record from database table, i want to select top 1 from resultset (the record after i extract from database table)
              • 4. Re: select top 1 record from resultset (PL/SQL, oracle)
                809036
                would you provide example for my reference, thanks
                • 5. Re: select top 1 record from resultset (PL/SQL, oracle)
                  SomeoneElse
                  How about if you provide real create table statements with sample data.

                  Then show us clearly what you want the result to be.
                  • 6. Re: select top 1 record from resultset (PL/SQL, oracle)
                    BluShadow
                    806033 wrote:
                    it just return top 1 record from database table, i want to select top 1 from resultset (the record after i extract from database table)
                    Your result set is based on what you select. Your select statement has not order by clause and therefore the data is in a random order by it's very nature, so there is no "top" record.

                    If you say:
                    select id from customer where surname ='WONG';

                    it returns:

                    2
                    3
                    4
                    and then you say you want it to return 2, then getting the min(id) will do that.

                    If you're logic is different to that then you need to explain with a better set of data and a better explanation of the logic.

                    Remember, a set of data is a set of data, it has no "top", no "bottom" and no "sides"... just a set of data. If you want it to have top, bottom etc. then you need to define that, typically using an order by clause in your query.

                    You've shown no other code to indicate a "resultset" other than the data returned from a query.

                    Read this: {message:id=9360002}
                    • 7. Re: select top 1 record from resultset (PL/SQL, oracle)
                      809036
                      yes. my result set is a set of data so i can't use order by or min function to extract top 1 record, because
                      i want to ensure the output list contains only 1 record, how can i do that?

                      if the result is one record, no problem. but sometimes it returns more than 1, then i need to select one of the record from the resultset and output from package.

                      any methods?
                      • 8. Re: select top 1 record from resultset (PL/SQL, oracle)
                        sb92075
                        806033 wrote:
                        yes. my result set is a set of data so i can't use order by or min function to extract top 1 record, because
                        i want to ensure the output list contains only 1 record, how can i do that?

                        if the result is one record, no problem. but sometimes it returns more than 1, then i need to select one of the record from the resultset and output from package.

                        any methods?
                        what criteria determines which 1 row out of many actually gets returned?
                        • 9. Re: select top 1 record from resultset (PL/SQL, oracle)
                          Billy~Verreynne
                          806033 wrote:
                          yes. my result set is a set of data so i can't use order by or min function to extract top 1 record, because
                          i want to ensure the output list contains only 1 record,
                          Please read what Blu has posted. You are very obviously not grasping the fundamentals he was conveying.
                          if the result is one record, no problem. but sometimes it returns more than 1, then i need to select one of the record from the resultset and output from package.
                          Rows (there are no records in a database) are retrieved randomly.

                          So the 1st access may see rows (3,2,1), the 2nd access see it as rows (2,3,1), the 3rd access as (1,3,2) and so on. THERE IS NO IMPLIED ORDER.

                          Which means the "top" row for the 1st access is (3), and (2) for the 2nd access, and (1) for the 3rd access. So the VERY SAME PROCESS can with different accesses to the table, see a different "top row".

                          Which means the "top row" used/returned by the process is RANDOM.

                          This does not make logical sense. This does not make business sense.

                          So the question is not how to return the top row. The question is: HOW DO YOU IDENTIFY WHICH IS THE TOP ROW?+

                          Answer that question - and forum members will be able to advise you how to do this.
                          • 10. Re: select top 1 record from resultset (PL/SQL, oracle)
                            user503635
                            Below is the example. However as Bily and other experts replied, the return result is RANDOM. You will get unpredictable return result for each run.
                            DECLARE
                              v_id NUMBER(8); 
                              cursor my_cur is select id from customer where surname ='WONG';
                            
                            BEGIN
                                
                               FOR my_rec IN my_cur LOOP
                                 
                                v_id := my_rec.id;
                                dbms_output.put_line('ID is '||v_id);
                                EXIT;
                                
                               END LOOP; 
                            
                            END;