difference between select single and select upto 1

[Former Member]

hi

exprts

please answer me

difference between select single and select upto 1 row

[Former Member]

Just search the forum.

Rob

[Former Member]

hi,

see if u rselecting table which has records like this

a 2 xyz

a 2 yzx

a 3 xyz

a 4 xyz

if u r slecting rows which has basically a 2 then when using slect single first system will get both the records and then it will show the first one among them as

a 2 xyz

but when u use select upto one rows directly it will show the a 2 xyz it will not go for the second one again that if selction matches with the first one it will show it it will not go for next

i think i am clear to u

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 20, 2008 4:26 PM

[Former Member]

Hi,

Select single

If SINGLE is specified, the resulting set has a single line. If the remaining additions to the SELECT command select more than one line from the database, the first line that is found is entered into the resulting set.

When SINGLE is being specified, the lines to be read should be clearly specified in the WHERE condition, for the sake of efficiency. When the data is read from a database table, the system does this by specifying comparison values for the primary key.

If accessing tables for which SAP buffering is planned for single records, the SAP buffer is bypassed if the addition SINGLE is not specified. This behavior depends on the current implementation of the database interface and may change in future releases. In particular, it should not be used to bypass the SAP buffer. You should use the explicit addition BYPASSING BUFFER for this instead.

The addition SINLGE is not permitted in a subquery.

... UP TO n ROWS

This addition restricts the number of rows in the result set. A data object of the Type i is expected for n. A positive number in n indicates the maximum number of rows in the result set. If n contains the value 0, all selected rows are passed to the result set. If n contains a negative number, an exception that cannot be handled is raised.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

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.

Mainly: to read data from

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, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Regards

Rohini.

[Former Member]

According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

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.

Mainly: to read data from

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, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Mainly: to check if entries exist.

Regards.

[Former Member]

Select single , we use when we have all key fields in WHERE clause

and UP TO 1 ROWS in case we don't have all key fields in WHERE clause.

Performance wise, UP TO 1 rows is considered better when we are not having all key fields in WHERE.

Reward points if useful

Regards

Taranam

[Former Member]

hi,

If SINGLE is specified, the resulting set has a single line. If the remaining additions to the SELECT command select more than one line from the database, the first line that is found is entered into the resulting set. The data objects specified after INTO may not be internal tables, and the APPENDING addition may not be used.

An exclusive lock can be set for this line using the FOR UPDATE addition when a single line is being read with SINGLE. The SELECT command is used in this case only if all primary key fields in logical expressions linked by AND are checked to make sure they are the same in the WHERE condition. Otherwise, the resulting set is empty and sy-subrc is set to 8. If the lock causes a deadlock, an exception occurs. If the FOR UPDATE addition is used, the SELECT command circumvents SAP buffering.

select single * upto one row selects only one row from the top...

In the data object dobj, the first subsequence is searched for which contents match those of sub_string. However, uppercase/lowercase lettering is not taken into consideration. The content of the data object dobj will be shifted as far possible left or right - depending on the specification in direction - until the byte or character sequence contained in sub_string is at the position that is at the beginning or end of the data object dobj before the shift.

For sub_string, a byte or character-type data object is expected. Closing blanks in data ojects of fixed length are taken into consideration. If sub_string is an empty string, the position in front of the first character or byte is found. To the left, no shift will take place, and to the right there will be a shift by the entire length of dobj.

hope it clears the doubt

regards

rohan malik

[Former Member]

Hi,

SELECT SINGLE returns the first matching row for the given condition and it may not be unique, if there are more matching rows for the given condition.

SELECT ... UP TO 1 ROWS retrieves all the matching records and applies aggregation and ordering and returns the first record.

Inorder to check for the existence of a record then it is better to use SELECT SINGLE than using SELECT ... UP TO 1 ROWS since it uses low memory and has better performance."

Regards.