How to Make sure only the First Record is Selected from a particular table have which can Mutiple Records of Same ID in DB2

Question

I am trying to Selected a specific event from a event able, which can have mutile records with same ID.

SQL query is something like this:

EXEC SQL
  SELECT ID, 
         NAME, 
         DATE, 
         TIME
    FROM EVENT 
   WHERE ID   = 100  
     AND NAME = 'John'
END-EXEC.

Query Result can be with Multiple records.

Result set:

ID    NAME    DATE       TIME
100   JOHN    2021-06-06 12.11.23
100   JOHN    2021-06-09 12.11.23
100   JOHN    2021-06-07 12.11.23

this is causing -811 to which i am continue my program logic. here i want to select the Latest Event i.e. 100 JOHN 2021-06-09 12.11.23

hence i applied ORDER BY clause.

EXEC SQL
  SELECT ID, 
         NAME, 
         DATE, 
         TIME
    FROM EVENT 
   WHERE ID   = 100  
     AND NAME = 'John'
   ORDER BY DATE DESC,
            TIME DESC 
END-EXEC.

which fetches Result set as:

ID    NAME    DATE       TIME
100   JOHN    2021-06-09 12.11.23
100   JOHN    2021-06-07 12.11.23
100   JOHN    2021-06-06 12.11.23

hence the first records that will be selected with be the 100 JOHN 2021-06-09 12.11.23, but strangly when i run the program, the second event i.e.100 JOHN 2021-06-07 12.11.23 is selected.

my requirnment is to have selected the latest event hecne i am orderning it by DESC, but still there is no success. Please Advice.

Answer 1

First, your queries are not valid SQL statements -- although perhaps your interface fixes that. You want AND in the WHERE clause rather than ,.

Second, you can fetch just one row using FETCH FIRST. So:

SELECT ID, NAME, DATE,  TIME
FROM EVENT 
WHERE ID = 100 AND NAME = 'John'
ORDER BY DATE DESC, TIME DESC 
FETCH FIRST 1 ROW ONLY;