Is it necessary for rownum = 1 with Query.createQuery().getSingleResult()? [closed]

Posted on

Question :

I’m a newie and I’m not really sure if MIN(My_Date) returns just one row if 2 or more dates are identical.

Reference is a FOREIGN KEY.

so to limit query:

SELECT MIN(Shipping_Date) 
FROM Com_Order 
WHERE Reference = 'X' and rownum = 1
GROUP BY Reference;

This second query seems to have same results just one value..

SELECT MIN(Shipping_Date) 
FROM Com_Order 
WHERE Reference = 'X';

I’m using JPA, so is it redundant to use rownum = 1 and GROUP BY Reference if I use Query.creatQuery().getSingleResult()? Are the 2 queries the same?


Answer :

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column.

The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query.

Test at SQLfiddle.

Leave a Reply

Your email address will not be published. Required fields are marked *