SELECT SINGLE vs UP TO 1 ROWS

Explanation

SELECT SINGLE is used when the WHERE condition identifies a unique record or when the developer intentionally wants one matching row. However, many developers misuse SELECT SINGLE with incomplete keys. This can return an arbitrary matching row depending on database behavior and access path. UP TO 1 ROWS is used when you explicitly want the first row based on an ORDER BY or selection logic. The important rule is: if the business logic needs a specific record, the WHERE condition and ORDER BY should make that specific record clear. In interviews, this topic checks whether a developer understands data correctness, not only syntax.

Code example

* Risky if key is incompleteSELECT SINGLE vbeln, erdat  FROM vbak  INTO @DATA(ls_vbak)  WHERE kunnr = @lv_kunnr. * Better when latest record is neededSELECT vbeln, erdat  FROM vbak  WHERE kunnr = @lv_kunnr  ORDER BY erdat DESCENDING  INTO TABLE @DATA(lt_vbak)  UP TO 1 ROWS.

Real project scenario

A program reads the latest condition record but uses SELECT SINGLE without ORDER BY. In QA it works because only one record exists, but in production it returns an older record. The correct design is to sort by validity date or use proper key conditions.

Common mistakes

- Using SELECT SINGLE with incomplete key. - Assuming SELECT SINGLE always returns the latest record. - Not using ORDER BY when order matters. - Ignoring business meaning of the selected row.

Best practices

- Use full key where possible. - Use ORDER BY when business logic depends on sequence. - Do not use SELECT SINGLE to hide multiple-record problems. - Validate uniqueness expectations with data model knowledge.

Interview angle

A strong answer explains uniqueness, incomplete keys and why ORDER BY is needed when the first/latest row matters.