Skip to Content
author's profile photo Former Member
Former Member

Select Single Vs Select upto 1 row

Hi All,

Please tell me which of the two statements is better if we want to retrieve only one record from a table.

Select single or Select upto 1 rows?

Regards,

Saurabh

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 24, 2008 at 05:50 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 25, 2008 at 10:48 AM

    There is a lot of confusion in the replies and also in the other thread.

    If yoou ask 'A versus B' then this implies that you can use both for the same task, which is here not really true.

    SELEC SINGLE should be used for SELECT with full primary key. where only 1 record CAN come back.

    UP TO 1 ROWS is the special case of UP TO n ROWS can be used with any WHERE condition and gives you the first record which is found.

    >The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the

    > relevant records that are defined by the WHERE clause or lack of, applies any aggregate,

    > ordering or grouping functions to them and then returns the first record of the resultant result

    > set.

    This in incorrect, the UP TO n ROWS does not read all, only if the ORDER BY is added, then you will get the first records in sort order which requires that all rfecords are read.

    If you use UP TO 1 ROWS with a WHERE condition which is fulfilled by many records, then the first record is usually found very fast, even faster than a SELECT SINGLE.

    If you use UP TO 1 ROWS or SELECT SINGLE with the same WHERE condition, then both are more or less the same.

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      If you use UP TO 1 ROWS with a WHERE condition which is fulfilled by many records, then the first record is usually found very fast, even faster than a SELECT SINGLE.

      Hey, I'm curious about this. What gets sent to the DBMS by the two forms to account for the difference? I seem to recall reading somewhere that SELECT SINGLE is implemented as a single DB fetch, whereas SELECT is a prepare with bindings followed by fetch. But my memory is quite fallible these days.

  • Posted on Jun 24, 2008 at 05:47 AM

    Mathuria,

    Select sengle is the better in performance way.

    Why because select single up to one row has taken little time in aggrigation after selecting all entries which lies in where condition.

    so select single single is the better in performance.

    Amit.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 24, 2008 at 01:57 PM

    hiii,

    Select single * from table

    It fetches single record from the database, based on the condition you specified in the where clause.

    The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

    The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause or lack of, applies any aggregate, ordering or grouping functions to them and then returns the first record of the resultant result set.

    Where as select * from table up to 1 row

    Fetches first record if the condition specified in the where clause is satisfied, otherwise it doesn't fetch any record.

    hopefully it clears your question..

    reward if helpful..

    thanks..

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 21, 2009 at 11:14 PM

    You need to think about what "better" means to you.

    If you are asking which is faster, the difference is likely to so negligable that unless you are going to perform this selection 1,000,000 times you really shouldn't care. If you will perform the operation 1,000,000 times, maybe you are better off not performing it individually 1,000,000 times thereby leading you to an entirely different performance optimization question.

    If you are asking from a logic standpoint, then this is only relevent if you expect to get more than one record back. In which case, do you care which record you get back?

    If the answer is no, you might want to double check that you shouldn't care. One case I can think of for that is checking for the existance of any records. In this case, the answer leads back to a performance concern and so long as you are hitting the leading field or fields of an index somewhere, you should be fine.

    If the answer is yes, then which record? The only examples I can think of for this are min/max type problems, because you can't effectively specify those with the grammar of the ABAP SELECT. In that case, SELECT ... UP TO 1 ROWS with an ORDER BY achieves the logical result that you want.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.