CODEWITHSUNDEEP
javamysqlsql
Cao Felix
Cao Felix

Reputation: 329

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

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

Upvotes: 0

Views: 430

Answers (3)

Matthew McPeak
Matthew McPeak

Reputation: 17934

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;

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

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.

Upvotes: 0

user6638270
user6638270

Reputation:

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.

Upvotes: 2

Related Questions