MS sql limit statment without order by and without top

Question

I've an ORM layer that can communicates with several DBs, MYSQL,ORACLE,MS SQL and
I'am usign pagging in my applications, what i didn't know that i can get results
as pages straightfully from the query, so am updating the ORM layer to fit the 3 DBs
I mentioned.

In MYSQL SELECT * FROM myTable limit 5,5
In ORACLE SELECT * FROM ( SELECT * FROM myTable as t WHERE rownum < 100 ) WEHRE rownum > 10
But in MS SQL I cant find a way to get of the ORDER BY in the query

I apologize for any syntax errors in the queries, they are from memory.

My question is, is there any way to get rid of the ORDER BY and get paging capabilities for MS SQL

Possibile duplicates http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — Nicola Cossu, Jan 22 '12 at 15:37

Christian Specht · Answer 1 · 2012-01-22T16:16:27.530

4

Please re-think what you are doing here.
No matter what database engine you're using, it makes absolutely no sense to try to get rid of ORDER BY when you want to use paging.

Rows in relational databases don't have a fixed order, you need to specify an ORDER BY clause if you want to receive rows in a certain order.
If you don't specify an ORDER BY clause, the database engine will return the rows in a random order. Often the order looks sensible (like, ordered by the primary key) and if you run the same query more than one time, the order might really be the same.

But in reality, this is all random, and you can't rely on this "pseudo-order" making any sense or being the same on each query.
So paging without specifying the order by yourself makes absolutely no sense.

edited Jan 22 '12 at 16:16

answered Jan 22 '12 at 15:40

Christian Specht

35,843
15
128
182

+1. This is SQL for total beginners. Use an order - it makes NO sense to not have one. Get a book about SQL, read it from page one, and dont work on an ORM unless you know the basics of databases. When I made my first ORM many years ago, I introduces a "natural order" into the schema, which was the default order that was applied when no orer was askd for. – TomTom Jan 22 '12 at 15:50
dud, am not a beginner, I just don't know how to use MS SQL, any way i have several indices, and the engine will order the result set on its own, depending on the indices, so do you know how to get rid of the order by in the query? – user963602 Jan 22 '12 at 16:35
3

If you want to make sure that the ordering is reliable, you can't get rid of the `ORDER BY`. Trust us. – Christian Specht Jan 22 '12 at 16:56

Mithrandir · Answer 2 · 2012-01-22T15:45:41.817

3

Try

SELECT 
  * 
FROM 
  ( SELECT *,ROW_NUMBER() OVER (ORDER BY <some_col>) as rownum FROM myTable ) t 
WHERE rownum >= UpperLimit AND  rownum <= LowerLimit

Replace <some_col> with some column in your table that defines the order in which you want to page through your data.

edited Jan 22 '12 at 15:45

answered Jan 22 '12 at 15:40

Mithrandir

24,869
6
50
66

My question was without "order by" – user963602 Jan 22 '12 at 16:35
2

Then you're question is quite, well, uninformed. It can't be done without any sort of ordering in SQL SERVER 2008R2 and below. SQL Server 2012 supports a limit-clause, maybe you're willing to wait ... – Mithrandir Jan 22 '12 at 16:41

score 1 · Answer 3 · answered Jan 22 '12 at 16:39

I am not sure what the problem is. You can write:

select TOP 10 * from T

This works fine.

This usually does not make sense because the specific order is not guaranteed. It can even change from query to query.

But it can make sense if all you care about is to get any 10 records from a particular table.

MS sql limit statment without order by and without top

3 Answers3