I have a query which appears to be working fine, but I'm wondering if there is a cleaner (or more proper) way to write it.
The goal is to find the record in table ZVBAPIUSW01 where the date (UPDPASS) and time (UPDPASSTIME) are the greatest based on a given user ID.
Here is what I have got so far:
SELECT SINGLE * FROM ZVBAPIUSW01
WHERE OBJID = ID
AND UPDPASS IN (
SELECT MAX( UPDPASS ) FROM ZVBAPIUSW01 WHERE OBJID = ID )
AND UPDPASSTIME IN (
SELECT MAX( UPDPASSTIME ) FROM ZVBAPIUSW01 WHERE OBJID = ID AND UPDPASS IN (
SELECT MAX( UPDPASS ) FROM ZVBAPIUSW01 WHERE OBJID = ID ) ).
Thanks for your help. Mike
You could try
SELECT foo bar baz
FROM ZVBAPIUSW01 UP TO 1 ROWS
INTO (l_foo, l_bar, l_baz)
WHERE OBJID = ID
ORDER BY updpass DESCENDING updpasstime DESCENDING.