Can SQL ORDER BY clause used with any column in a data source?

Question

I am trying to read 1000 records from any database at a time. I assume we don't know the name of columns and type of columns so I always pick the first one in the table to do the ORDER BY. Does it matter which one in the table to pick for using ORDER BY? I am not very good at database stuff and right now I am learning. If picking a good column can speed up the query execution to return the result set, then what type should I be looking for?

My codes:

if (SQLServerDatabase) {

    String query = "SELECT * FROM "+tablename+" ORDER BY "+columnnames.get(0)+" OFFSET "+offset+" ROWS FETCH NEXT "+size+" ROWS ONLY;";

    rs = stmt.executeQuery(query);

} else if (MySQLDatabase) {

    String query = "SELECT * FROM "+tablename+" LIMIT "+offset+", "+size+";";

    rs = stmt.executeQuery(query);

}

Note: columnnames is a list

ORDER BY is fast on indexed columns, slow on others. First column is often, but not always, the primary key - and fast. — jarlh, Aug 22 '16 at 13:14
Apart from the performance issue if you use an arbitrary, unknown column for the ordering, does this make sense at all? Why don't you know it? — Tim Schmelter, Aug 22 '16 at 13:15
The question is, when you do not know what are you ordering by, is it useful to order the result at all? — Martin Pohorský, Aug 22 '16 at 13:17
I agree with Tim and Martin, but it requires to use ORDER BY when using OFFSET and FETCH. (ORDER BY is mandatory to use OFFSET and FETCH clause. https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx) So I am not sure how you can read some number of records from a SQL Server database. If I do "SELECT * FROM tablename" to get everything from a table and the table contains 1 million records... how can I do it faster? — Cao Felix, Aug 22 '16 at 13:28
Adding more conditions to your query would significantly increase its performance as well. I know this will be hard since your query is dynamic. So another suggestion is to make more custom sql queries in your java code, to cover exactly what you need to select and\or display for whatever you use your data for. This will also increase the queries performance and perhaps security(?)(Correct me on this anyone, but does not dynamic sql open up the possibility for sql injections?) I know it avoids your question completely, but sometimes dynamic is not the way to go, in my opinion. — Moptan, Aug 22 '16 at 13:35
Also be careful with sql injection here. Building strings like this can cause some nasty situations. — Sean Lange, Aug 22 '16 at 13:37

score 2 · Accepted Answer · edited May 23 '17 at 11:51

I would suggest first getting the name of the primary key, and doing ORDER BY on that.

This answer should point you in the right direction. SQL Server: Get table primary key using sql query

However, you need to be aware that what you are doing does not guarantee that you do not retrieve duplicate records. For example, if you are asking for records 1-1,000, based on any order, and another record is inserted into the table in the meantime, which would come in the first 1,000, then when you ask for records 1,001 - 2,000 you will find that record 1,001 is the same as record 1,000.

By the way, I use setAutoCommit(false) in here for this solution. Not sure if that is a good idea. — Cao Felix, Aug 26 '16 at 20:13

score 0 · Answer 2 · answered Aug 22 '16 at 13:44

I would think you could use any valid expression in the ORDER BY clause.

If you don't care about ordering, you could use (SELECT NULL) as an expression in the ORDER BY, for example:

 SELECT * FROM mytable ORDER BY (SELECT NULL) OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

There's no guarantee that the query will return rows in the same order each time its executed. If you're doing this to implement "paging", there's potential to miss (skip over) some rows, and to also to return the same rows on a subsequent execution.

score 0 · Answer 3 · answered Aug 22 '16 at 15:21

I'll second everyone's doubts regarding the usefulness of sorting by columns you cannot identify in advance. That's especially true because the first column may allow duplicates, meaning the results will still be unsorted within each distinct value of that first column.

That all said, I'm surprised no one mentioned this: if you want to sort by the first column in your query, you don't need to actually identify the name of the 1st column. You just write ORDER BY 1.

E.g.,

CREATE TABLE test_sort ( a number, b date, c varchar2(30) );

INSERT INTO test_sort VALUES ( 1, SYSDATE - 1, 'GGGG');
INSERT INTO test_sort VALUES ( 2, SYSDATE + 4, 'QQQQ');
INSERT INTO test_sort VALUES ( 3, SYSDATE - 7, 'ZZZZ');
INSERT INTO test_sort VALUES ( 4, SYSDATE - 12, 'BBBB');

SELECT a, b, c FROM test_sort
ORDER BY 1;

SELECT b, c, a FROM test_sort
ORDER BY 1;

SELECT c, a, b FROM test_sort
ORDER BY 1;

Can SQL ORDER BY clause used with any column in a data source?

3 Answers3