How Universal Is the Limit Statement in SQL

How universal is the LIMIT statement in SQL?

http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows lists all of the major variants of the select command.

I believe the best way to do this is to use the SET ROWCOUNT command before your SELECT statement.

So, for you:

SET ROWCOUNT 1
SELECT %s FROM %s

Limit on the WHERE col IN (...) condition

Depending on the database engine you are using, there can be limits on the length of an instruction.

SQL Server has a very large limit:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

ORACLE has a very easy to reach limit on the other side.

So, for large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.

Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?

this shows the different ways:

-- DB2
select * from table fetch first 10 rows only 
-- Informix 
select first 10 * from table 
-- Microsoft SQL Server and Access 
select top 10 * from table 
-- MySQL and PostgreSQL 
select * from table limit 10 
-- Oracle 
select * from (select * from table) where rownum <= 10

SQLAlchemy query to return only n results?

for sqlalchemy >= 1.0.13
Use the limit method.

query(Model).filter(something).limit(5).all()

How to skip the first n rows in sql query

Query: in sql-server

DECLARE @N INT = 5 --Any random number

SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
              , ID --Add any fields needed here (or replace ID by *)
        FROM TABLE_NAME
) AS tbl 
WHERE @N < RoNum
ORDER BY tbl.ID

This will give rows of Table, where rownumber is starting from @N + 1.

Using IN Condition with large number of items

What are my options here besides what I am doing in the above?

They are limited - you could chain a LONG sequence of OR id = X calls but you may have SIGNIFICANT performance problems compared to IN.

The best option performance-wise is to join to a table (parameter, temporary, CTE, inline UNION, or static) on the server side. However that's not always an option depending on how you're executing queries and what permissions you have.

Of course, you can also execute a separate query for each value, which would be easier to code but might have performance problems as well.

Are the limitations of max items set at the DB level, the type of DB, what?

That is platform-specific so there's not a universal answer

Should I do in () OR in () OR in(). That doesn't seem too slick. Are all of these options viable?

That is one option to add to the others above - you'd have to try it to see what the performance impact is.

I've read a bit about possibly using temp tables to do this but without any examples. How would I load data into a temp table to then join into the table I'm wanting to get the data from?

That depends on the platform, your permissions, your API available, etc.

combine into a single SQL query both the first date and the total number of dates that are returned

how about,

SELECT MIN(DateAdded), COUNT(*) FROM table WHERE id = x

---

Since you don't specifiy which engine you are using, this also avoids the far from standardized LIMIT keyword.

---

Related Topics