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

Question

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';

Different results... As you have noticed: ORDER BY G.anothertime — jarlh, Dec 02 '20 at 14:08
Use EXPLAIN PLAN (whatever PLSQL's version of that is) on each of the queries using your own data, and compare the results. But it should be pretty clear anyway; the first version does multiple SELECTs and an ORDER BY, while the second does a single SELECT. — Ken White, Dec 02 '20 at 14:12
And I rolled back your changes, because this would change your request completely. I.e. this would be another question. So make another request for it. — Thorsten Kettner, Dec 03 '20 at 06:34
As is, the two queries do different things. Each is appropriate for its task. The first query, however, runs in Oracle only, because of `rownum`, which is only available in Oracle and because Oracle violates the SQL standard in order to gurantee an ordered subquery result for `rownum` to work. Please see the two answers for better solutions. — Thorsten Kettner, Dec 03 '20 at 06:36
I've added the `oracle` tag to show which DBMS you are asking this SQL question for. I've removed the `database` tag, because you are not asking how databases work, and I've removed the `plsql` tag, because this is about SQL, not Oracle's programming language PL/SQL. With SQL questions it is often helpful, too, to tell us the version. There are tags for different Oracle versions available you can add. — Thorsten Kettner, Dec 03 '20 at 06:39
Is there a reason for using quotes on the 1 by the way? If the column `recordtype` is numeric, then you should use a number instead, i.e. `WHERE G.recordtype= 1`. — Thorsten Kettner, Dec 03 '20 at 06:57
recordtype was character on system before. We use numbers now for this field as a result of DB Management decision. Version : Oracle 19C. Thanks for your reply. — Serol kırçıl, Dec 03 '20 at 10:25

score 1 · Answer 1 · answered Dec 02 '20 at 14:16

1

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.

answered Dec 02 '20 at 14:16

Gordon Linoff

1,242,037
58
646
786

I'd probably make this `KEEP (DENSE_RANK LAST ORDER BY anothertime)` to get the last row. It does exactly the same thing of course, but I consider it a tad more straight-on. – Thorsten Kettner Dec 03 '20 at 06:51

score 1 · Answer 2 · answered Dec 03 '20 at 03:43

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;

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

2 Answers2