how to limit records set in SQL WHERE clause

Question

I've got software where I can provide SQL queries to db. But there is limitation, I can only change WHERE clause... for ex.: [Python] x = gp.searchcursor('DATABASE_NAME','FIELDS WHICH I WANT TO VIEW','WHERE_CLAUSE')

I've got table where is about ~250k records and I want to get first 50-70 (number doesnt matter) of records. My question is how can I create query (actually WHERE clause) which will return mi those records?

just for clarify, on the bottom of application there is Oracle and MS SQL server (depends of called database)

Do you know from which table the fields are fetched ? If yes, post the schema here. — Ravi Singh, Jul 31 '13 at 08:31
I'm calling only one table each time, and I fetch fields depends on my needs — Krystian, Jul 31 '13 at 08:33
@Krystian : Is there is a primary key on table ? Use it in where_clause. — Ravi Singh, Jul 31 '13 at 08:37
it could be done it that way but I can not guarantee that ids beetwen 0-50 = 50records (record can be deleted or moved...) — Krystian, Jul 31 '13 at 08:37
http://stackoverflow.com/questions/7680672/sql-query-to-select-first-top-n-records — Romans 8.38-39, Jul 31 '13 at 08:44
WHERE ROWNUM<=50 (Oracle Syntax - http://www.w3schools.com/sql/sql_top.asp) — Romans 8.38-39, Jul 31 '13 at 08:50

Nenad Zivkovic · Accepted Answer · 2013-07-31T08:53:52.650

2

Maybe you can do the trick using a subquery in WHERE

WHERE YourID IN (SELECT TOP 50 YourID FROM YourTable WHERE YourActualConditions)

This will work for SQL Server, Oracle syntax is slightly different:

WHERE YourID IN (SELECT YourID FROM YourTable WHERE YourActualConditions AND ROWNUM<=50)

EDIT Actually in Oracle you wouldn't even need a subquery:

 WHERE YourActualConditions AND ROWNUM<=50

should be enough

edited Jul 31 '13 at 08:53

answered Jul 31 '13 at 08:47

Nenad Zivkovic

18,221
6
42
55

how to limit records set in SQL WHERE clause

1 Answers1