2

I have a table with no PK column. I am not supposed to change that. There is also a numeric column which can be used to order the rows. How do i select the top 1 or top n rows without using any function, ie a database agnostic query ?

I looked at this query, but it does not work for my case - Can there be a database-agnostic SQL query to fetch top N rows?

Community
  • 1
  • 1
sequel.learner
  • 3,421
  • 7
  • 22
  • 24
  • 2
    You can use the (ANSI standard SQL) `ORDER BY someColumn OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;` but it's not supported by all DBMS. – ypercubeᵀᴹ Jun 12 '13 at 09:06
  • @ypercube - as an aside, was wondering if we could have one SUPER SQL platform and SUPER SQL language which would serve as a front end to all RDBMS. You can use one language to query all DBs...and that too DBs of different brands. Does such a thing exist ? – sequel.learner Jun 12 '13 at 20:45

1 Answers1

2

Per the thread you linked to, there isn't much of a database agnostic solution:

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

http://forums.mysql.com/read.php?60,4515,4678

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • How do I also do an order by some column in each of the above queries ? I think that is a must when we use top or top-like query. Without it, the above queries are partially useful. In SQL server, just add an order by column after the from. what about the others ? – sequel.learner Jun 12 '13 at 09:01
  • @sequel.learner: the same for the others. (see ypercube's edit) – Denis de Bernardy Jun 12 '13 at 09:36
  • 1
    @Denis: Postgres (from 8.4) and SQL-Server (2012 version) support the `OFFSET / FETCH` syntax. – ypercubeᵀᴹ Jun 12 '13 at 09:39
  • @sequel.learner: I wouldn't know. I haven't used Oracle in years. :-P More seriously, certainly yes, for backwards compatibility reasons. – Denis de Bernardy Jun 12 '13 at 20:47