CODEWITHSUNDEEP
sqloracle-database
Serol kırçıl
Serol kırçıl

Reputation: 1

Which one is better performance and clean code example of SQL?

Sorry i am newbee.

Which one ise better performance and clean code example of sql?

Thanks.

SELECT batchlatesttime
  FROM (SELECT G.batchlatesttime
          FROM table G
         WHERE G.recordtype= '1'
         ORDER BY G.anothertime DESC) --I dont know why we sort by anothertime
 WHERE ROWNUM < 2;

SELECT max(G.batchlatesttime)
  FROM table G
 WHERE G.recordtype= '1';

Upvotes: 0

Views: 55

Answers (2)

Shaun Peterson
Shaun Peterson

Reputation: 1790

Depending on your oracle version then you can also write it as below,

From Oracle 12.1 onwards we have the FETCH FIRST x rows only. (for a better explaination see the excepted answer on How do I limit the number of rows returned by an Oracle query after ordering?)

If you are using a version prior to 12.1 then ignore this.

SELECT G.batchlatesttime
FROM table G
WHERE G.recordtype= '1'
ORDER BY G.anothertime DESC
FETCH FIRST 1 ROWS ONLY;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The equivalent query to the first is:

SELECT MAX(G.batchlatesttime) KEEP (DENSE_RANK FIRST ORDER BY anothertime DESC)
FROM table G
WHERE G.recordtype = '1';

This would probably be considered the "cleanest" solution -- although there is obviously room for disagreement -- because it avoids a subquery.

Upvotes: 1

Related Questions