<p>I'm interested in learning some (ideally) database agnostic ways of selecting the <em>n</em>th row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: </p>

<ul>
<li>SQL Server</li>
<li>MySQL</li>
<li>PostgreSQL</li>
<li>SQLite</li>
<li>Oracle</li>
</ul>

<p>I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:</p>

<pre><code>WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000
</code></pre>

<p>Credit for the above SQL: <a href="https://web.archive.org/web/20101103031717/http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx" rel="noreferrer">Firoz Ansari's Weblog</a></p>

<p><strong>Update:</strong> See <a href="https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table#42765">Troels Arvin's answer</a> regarding the SQL standard. <em>Troels, have you got any links we can cite?</em></p>

[{'answer_id': 16570, 'author': 'Andrew G. Johnson', 'author_id': 428190, 'author_profile': 'https://Stackoverflow.com/users/428190', 'pm_score': 2, 'selected': False, 'text': '<p>ADD:</p>\n\n<pre><code>LIMIT n,1\n</code></pre>\n\n<p>That will limit the results to one result starting at result n.</p>\n'}, {'answer_id': 16582, 'author': 'Kibbee', 'author_id': 1862, 'author_profile': 'https://Stackoverflow.com/users/1862', 'pm_score': 1, 'selected': False, 'text': "<p>LIMIT n,1 doesn't work in MS SQL Server.  I think it's just about the only major database that doesn't support that syntax.  To be fair, it isn't part of the SQL standard, although it is so widely supported that it should be.  In everything except SQL server LIMIT works great.  For SQL server, I haven't been able to find an elegant solution.</p>\n"}, {'answer_id': 16584, 'author': 'Greg Hurlman', 'author_id': 35, 'author_profile': 'https://Stackoverflow.com/users/35', 'pm_score': 2, 'selected': False, 'text': "<p>Here's a generic version of a sproc I recently wrote for Oracle that allows for dynamic paging/sorting - HTH</p>\n\n<pre><code>-- p_LowerBound = first row # in the returned set; if second page of 10 rows,\n--                this would be 11 (-1 for unbounded/not set)\n-- p_UpperBound = last row # in the returned set; if second page of 10 rows,\n--                this would be 20 (-1 for unbounded/not set)\n\nOPEN o_Cursor FOR\nSELECT * FROM (\nSELECT\n    Column1,\n    Column2\n    rownum AS rn\nFROM\n(\n    SELECT\n        tbl.Column1,\n        tbl.column2\n    FROM MyTable tbl\n    WHERE\n        tbl.Column1 = p_PKParam OR\n        tbl.Column1 = -1\n    ORDER BY\n        DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 1, Column1, 'X'),'X'),\n        DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 1, Column1, 'X'),'X') DESC,\n        DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate),\n        DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate) DESC\n))\nWHERE\n    (rn &gt;= p_lowerBound OR p_lowerBound = -1) AND\n    (rn &lt;= p_upperBound OR p_upperBound = -1);\n</code></pre>\n"}, {'answer_id': 16587, 'author': 'Ellen Teapot', 'author_id': 1914, 'author_profile': 'https://Stackoverflow.com/users/1914', 'pm_score': 5, 'selected': False, 'text': "<p>I'm not sure about any of the rest, but I know SQLite and MySQL don't have any &quot;default&quot; row ordering. In those two dialects, at least, the following snippet grabs the 15th entry from the_table, sorting by the date/time it was added:</p>\n<pre><code>SELECT * \nFROM the_table \nORDER BY added DESC \nLIMIT 1,15\n</code></pre>\n<p>(of course, you'd need to have an added DATETIME field, and set it to the date/time that entry was added...)</p>\n"}, {'answer_id': 16602, 'author': 'Tim', 'author_id': 1970, 'author_profile': 'https://Stackoverflow.com/users/1970', 'pm_score': 4, 'selected': False, 'text': '<p>I suspect this is wildly inefficient but is quite a simple approach, which worked on a small dataset that I tried it on.</p>\n\n<pre><code>select top 1 field\nfrom table\nwhere field in (select top 5 field from table order by field asc)\norder by field desc\n</code></pre>\n\n<p>This would get the 5th item, change the second top number to get a different nth item</p>\n\n<p>SQL server only (I think) but should work on older versions that do not support ROW_NUMBER().</p>\n'}, {'answer_id': 16606, 'author': 'Neall', 'author_id': 619, 'author_profile': 'https://Stackoverflow.com/users/619', 'pm_score': 7, 'selected': False, 'text': '<p>PostgreSQL supports <a href="https://www.postgresql.org/docs/current/tutorial-window.html" rel="noreferrer">windowing functions</a> as defined by the SQL standard, but they\'re awkward, so most people use (the non-standard) <a href="http://www.postgresql.org/docs/current/static/queries-limit.html" rel="noreferrer"><code>LIMIT</code> / <code>OFFSET</code></a>:</p>\n\n<pre><code>SELECT\n    *\nFROM\n    mytable\nORDER BY\n    somefield\nLIMIT 1 OFFSET 20;\n</code></pre>\n\n<p>This example selects the 21st row. <code>OFFSET 20</code> is telling Postgres to skip the first 20 records. If you don\'t specify an <code>ORDER BY</code> clause, there\'s no guarantee which record you will get back, which is rarely useful.</p>\n'}, {'answer_id': 16608, 'author': 'Adam V', 'author_id': 517, 'author_profile': 'https://Stackoverflow.com/users/517', 'pm_score': 3, 'selected': False, 'text': '<p>When we used to work in MSSQL 2000, we did what we called the "triple-flip":</p>\n\n<p><strong>EDITED</strong></p>\n\n<pre><code>DECLARE @InnerPageSize int\nDECLARE @OuterPageSize int\nDECLARE @Count int\n\nSELECT @Count = COUNT(&lt;column&gt;) FROM &lt;TABLE&gt;\nSET @InnerPageSize = @PageNum * @PageSize\nSET @OuterPageSize = @Count - ((@PageNum - 1) * @PageSize)\n\nIF (@OuterPageSize &lt; 0)\n    SET @OuterPageSize = 0\nELSE IF (@OuterPageSize &gt; @PageSize)\n    SET @OuterPageSize = @PageSize\n\nDECLARE @sql NVARCHAR(8000)\n\nSET @sql = \'SELECT * FROM\n(\n    SELECT TOP \' + CAST(@OuterPageSize AS nvarchar(5)) + \' * FROM\n    (\n        SELECT TOP \' + CAST(@InnerPageSize AS nvarchar(5)) + \' * FROM &lt;TABLE&gt; ORDER BY &lt;column&gt; ASC\n    ) AS t1 ORDER BY &lt;column&gt; DESC\n) AS t2 ORDER BY &lt;column&gt; ASC\'\n\nPRINT @sql\nEXECUTE sp_executesql @sql\n</code></pre>\n\n<p>It wasn\'t elegant, and it wasn\'t fast, but it worked.</p>\n'}, {'answer_id': 16735, 'author': 'Mark Harrison', 'author_id': 116, 'author_profile': 'https://Stackoverflow.com/users/116', 'pm_score': 2, 'selected': False, 'text': '<p>Oracle:</p>\n\n<pre><code>select * from (select foo from bar order by foo) where ROWNUM = x\n</code></pre>\n'}, {'answer_id': 16752, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>But really, isn't all this really just parlor tricks for good database design in the first place?  The few times I needed functionality like this it was for a simple one off query to make a quick report.  For any real work, using tricks like these is inviting trouble.  If selecting a particular row is needed then just have a column with a sequential value and be done with it.</p>\n"}, {'answer_id': 16753, 'author': 'Graeme Perrow', 'author_id': 1821, 'author_profile': 'https://Stackoverflow.com/users/1821', 'pm_score': 1, 'selected': False, 'text': "<p>In Sybase SQL Anywhere:</p>\n\n<pre><code>SELECT TOP 1 START AT n * from table ORDER BY whatever\n</code></pre>\n\n<p>Don't forget the ORDER BY or it's meaningless.</p>\n"}, {'answer_id': 16777, 'author': 'Henrik Gustafsson', 'author_id': 2010, 'author_profile': 'https://Stackoverflow.com/users/2010', 'pm_score': 10, 'selected': True, 'text': '<p>There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.</p>\n\n<p>A really good site that talks about this and other things is <a href="http://troels.arvin.dk/db/rdbms/#select-limit" rel="noreferrer">http://troels.arvin.dk/db/rdbms/#select-limit</a>.</p>\n\n<p>Basically, PostgreSQL and MySQL supports the non-standard:</p>\n\n<pre><code>SELECT...\nLIMIT y OFFSET x \n</code></pre>\n\n<p>Oracle, DB2 and MSSQL supports the standard windowing functions:</p>\n\n<pre><code>SELECT * FROM (\n  SELECT\n    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,\n    columns\n  FROM tablename\n) AS foo\nWHERE rownumber &lt;= n\n</code></pre>\n\n<p>(which I just copied from the site linked above since I never use those DBs)</p>\n\n<p><em>Update:</em> As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.</p>\n\n<p><em>Update:</em> SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.</p>\n'}, {'answer_id': 16780, 'author': 'Nick Berardi', 'author_id': 17, 'author_profile': 'https://Stackoverflow.com/users/17', 'pm_score': 4, 'selected': False, 'text': '<p>1 small change: n-1 instead of n.</p>\n\n<pre><code>select *\nfrom thetable\nlimit n-1, 1\n</code></pre>\n'}, {'answer_id': 42765, 'author': 'Troels Arvin', 'author_id': 4462, 'author_profile': 'https://Stackoverflow.com/users/4462', 'pm_score': 4, 'selected': False, 'text': '<p>Contrary to what some of the answers claim, the SQL standard is not silent regarding this subject. </p>\n\n<p>Since SQL:2003, you have been able to use "window functions" to skip rows and limit result sets. </p>\n\n<p>And in SQL:2008, a slightly simpler approach had been added, using<code><br>\n  OFFSET <em>skip</em> ROWS\n  FETCH FIRST <em>n</em> ROWS ONLY</code></p>\n\n<p>Personally, I don\'t think that SQL:2008\'s addition was really needed, so if I were ISO, I would have kept it out of an already rather large standard.</p>\n'}, {'answer_id': 618857, 'author': 'jrEving', 'author_id': 72739, 'author_profile': 'https://Stackoverflow.com/users/72739', 'pm_score': 0, 'selected': False, 'text': '<p>unbelievable that you can find a SQL engine executing this one ...</p>\n\n<pre><code>WITH sentence AS\n(SELECT \n    stuff,\n    row = ROW_NUMBER() OVER (ORDER BY Id)\nFROM \n    SentenceType\n    )\nSELECT\n    sen.stuff\nFROM sentence sen\nWHERE sen.row = (ABS(CHECKSUM(NEWID())) % 100) + 1\n</code></pre>\n'}, {'answer_id': 965955, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<pre><code>SELECT * FROM emp a\nWHERE  n = ( \n    SELECT COUNT( _rowid)\n    FROM emp b\n    WHERE a. _rowid &gt;= b. _rowid\n);\n</code></pre>\n'}, {'answer_id': 1028336, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>For SQL Server, a generic way to go by row number is as such:</p>\n\n<pre><code>SET ROWCOUNT @row --@row = the row number you wish to work on.\n</code></pre>\n\n<p>For Example:</p>\n\n<pre><code>set rowcount 20   --sets row to 20th row\n\nselect meat, cheese from dbo.sandwich --select columns from table at 20th row\n\nset rowcount 0   --sets rowcount back to all rows\n</code></pre>\n\n<p>This will return the 20th row's information. Be sure to put in the rowcount 0 afterward.</p>\n"}, {'answer_id': 1104447, 'author': 'monibius', 'author_id': 135569, 'author_profile': 'https://Stackoverflow.com/users/135569', 'pm_score': 5, 'selected': False, 'text': '<p>SQL 2005 and above has this feature built-in. Use the ROW_NUMBER() function. It is excellent for web-pages with a &lt;&lt; Prev and Next >> style browsing:</p>\n\n<p>Syntax:</p>\n\n<pre><code>SELECT\n    *\nFROM\n    (\n        SELECT\n            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,\n            *\n        FROM\n            Table_1\n    ) sub\nWHERE\n    RowNum = 23\n</code></pre>\n'}, {'answer_id': 4228433, 'author': 'Sangeeth Krishna', 'author_id': 513879, 'author_profile': 'https://Stackoverflow.com/users/513879', 'pm_score': 1, 'selected': False, 'text': "<p>T-SQL - Selecting N'th RecordNumber from a Table </p>\n\n<pre><code>select * from\n (select row_number() over (order by Rand() desc) as Rno,* from TableName) T where T.Rno = RecordNumber\n\nWhere  RecordNumber --&gt; Record Number to Select\n       TableName --&gt; To be Replaced with your Table Name\n</code></pre>\n\n<p>For e.g. to select 5 th record from a table Employee, your query should be </p>\n\n<pre><code>select * from\n (select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5\n</code></pre>\n"}, {'answer_id': 8677680, 'author': 'E-A', 'author_id': 584508, 'author_profile': 'https://Stackoverflow.com/users/584508', 'pm_score': 2, 'selected': False, 'text': '<p>For example, if you want to select every 10th row in MSSQL, you can use;</p>\n\n<pre><code>SELECT * FROM (\n  SELECT\n    ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2\n  FROM TableName\n) AS foo\nWHERE rownumber % 10 = 0\n</code></pre>\n\n<p>Just take the MOD and change number 10 here any number you want.</p>\n'}, {'answer_id': 10633023, 'author': 'Amit Shah', 'author_id': 1240318, 'author_profile': 'https://Stackoverflow.com/users/1240318', 'pm_score': 3, 'selected': False, 'text': '<p>Here is a fast solution of your confusion.</p>\n<pre><code>SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1\n</code></pre>\n<p>Here You may get Last row by Filling N=0, Second last by N=1, Fourth Last By Filling N=3 and so on.</p>\n<p>This is very common question over the interview and this is Very simple ans of it.</p>\n<p>Further If you want Amount, ID or some Numeric Sorting Order than u may go for CAST function in MySQL.</p>\n<pre><code>SELECT DISTINCT (`amount`) \nFROM cart \nORDER BY CAST( `amount` AS SIGNED ) DESC \nLIMIT 4 , 1\n</code></pre>\n<p>Here By filling N = 4 You will be able to get Fifth Last Record of Highest Amount from CART table. You can fit your field and table name and come up with solution.</p>\n'}, {'answer_id': 21870925, 'author': 'Aditya', 'author_id': 2819400, 'author_profile': 'https://Stackoverflow.com/users/2819400', 'pm_score': 3, 'selected': False, 'text': "<blockquote>\n  <p><strong>SQL SERVER</strong></p>\n</blockquote>\n\n<hr>\n\n<p>Select n' th record from top</p>\n\n<pre><code>SELECT * FROM (\nSELECT \nID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW\nFROM TABLE \n) AS TMP \nWHERE ROW = n\n</code></pre>\n\n<p>select n' th record from bottom</p>\n\n<pre><code>SELECT * FROM (\nSELECT \nID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW\nFROM TABLE \n) AS TMP \nWHERE ROW = n\n</code></pre>\n"}, {'answer_id': 26402677, 'author': 'Rameshwar Pawale', 'author_id': 1794528, 'author_profile': 'https://Stackoverflow.com/users/1794528', 'pm_score': 4, 'selected': False, 'text': '<p>Verify it on SQL Server:</p>\n\n<pre><code>Select top 10 * From emp \nEXCEPT\nSelect top 9 * From emp\n</code></pre>\n\n<p>This will give you 10th ROW of emp table!</p>\n'}, {'answer_id': 28210751, 'author': 'Arjun Chiddarwar', 'author_id': 2149459, 'author_profile': 'https://Stackoverflow.com/users/2149459', 'pm_score': 1, 'selected': False, 'text': "<pre><code>SELECT\n    top 1 *\nFROM\n    table_name\nWHERE\n    column_name IN (\n        SELECT\n            top N column_name\n        FROM\n            TABLE\n        ORDER BY\n            column_name\n    )\nORDER BY\n    column_name DESC\n</code></pre>\n\n<p>I've written this query for finding Nth row.\nExample with this query would be</p>\n\n<pre><code>SELECT\n    top 1 *\nFROM\n    Employee\nWHERE\n    emp_id IN (\n        SELECT\n            top 7 emp_id\n        FROM\n            Employee\n        ORDER BY\n            emp_id\n    )\nORDER BY\n    emp_id DESC\n</code></pre>\n"}, {'answer_id': 32888597, 'author': 'THE JOATMON', 'author_id': 736893, 'author_profile': 'https://Stackoverflow.com/users/736893', 'pm_score': 2, 'selected': False, 'text': '<p>Nothing fancy, no special functions, in case you use Caché like I do...</p>\n\n<pre><code>SELECT TOP 1 * FROM (\n  SELECT TOP n * FROM &lt;table&gt;\n  ORDER BY ID Desc\n)\nORDER BY ID ASC\n</code></pre>\n\n<p>Given that you have an ID column or a datestamp column you can trust.</p>\n'}, {'answer_id': 40680413, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>This is how I'd do it within DB2 SQL, I believe the RRN (relative record number) is stored within the table by the O/S;</p>\n<pre><code>SELECT * FROM (                        \n               SELECT RRN(FOO) AS RRN, FOO.*\n               FROM FOO                         \n               ORDER BY RRN(FOO)) BAR             \nWHERE BAR.RRN = recordnumber\n</code></pre>\n"}, {'answer_id': 44336943, 'author': 'Dwipam Katariya', 'author_id': 7530100, 'author_profile': 'https://Stackoverflow.com/users/7530100', 'pm_score': 0, 'selected': False, 'text': '<pre><code>select * from \n(select * from ordered order by order_id limit 100) x order by \nx.order_id desc limit 1;\n</code></pre>\n\n<p>First select top 100 rows by ordering in ascending and then select last row by ordering in descending and limit to 1. However this is a very expensive statement as it access the data twice.</p>\n'}, {'answer_id': 45137397, 'author': 'John Deighan', 'author_id': 1738579, 'author_profile': 'https://Stackoverflow.com/users/1738579', 'pm_score': 0, 'selected': False, 'text': '<p>It seems to me that, to be efficient, you need to 1) generate a random number between 0 and one less than the number of database records, and 2) be able to select the row at that position. Unfortunately, different databases have different random number generators and different ways to select a row at a position in a result set - usually you specify how many rows to skip and how many rows you want, but it\'s done differently for different databases. Here is something that works for me in SQLite:</p>\n\n<pre><code>select * \nfrom Table \nlimit abs(random()) % (select count(*) from Words), 1;\n</code></pre>\n\n<p>It does depend on being able to use a subquery in the limit clause (which in SQLite is LIMIT &lt;recs to skip&gt;,&lt;recs to take&gt;) Selecting the number of records in a table should be particularly efficient, being part of the database\'s meta data, but that depends on the database\'s implementation. Also, I don\'t know if the query will actually build the result set before retrieving the Nth record, but I would hope that it doesn\'t need to. Note that I\'m not specifying an "order by" clause. It might be better to "order by" something like the primary key, which will have an index - getting the Nth record from an index might be faster if the database can\'t get the Nth record from the database itself without building the result set.</p>\n'}, {'answer_id': 48622884, 'author': 'Kaushik Nayak', 'author_id': 7998591, 'author_profile': 'https://Stackoverflow.com/users/7998591', 'pm_score': 3, 'selected': False, 'text': '<p>In Oracle 12c, You may use <code>OFFSET..FETCH..ROWS</code>  option with <code>ORDER BY</code></p>\n\n<p>For example, to get the 3rd  record from top:</p>\n\n<pre><code>SELECT * \nFROM   sometable\nORDER BY column_name\nOFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;\n</code></pre>\n'}, {'answer_id': 50954231, 'author': 'nPcomp', 'author_id': 5074973, 'author_profile': 'https://Stackoverflow.com/users/5074973', 'pm_score': 2, 'selected': False, 'text': '<p>For SQL server, the following will return the first row from giving table.</p>\n\n<pre><code>declare @rowNumber int = 1;\n    select TOP(@rowNumber) * from [dbo].[someTable];\nEXCEPT\n    select TOP(@rowNumber - 1) * from [dbo].[someTable];\n</code></pre>\n\n<p>You can loop through the values with something like this: </p>\n\n<pre><code>WHILE @constVar &gt; 0\nBEGIN\n    declare @rowNumber int = @consVar;\n       select TOP(@rowNumber) * from [dbo].[someTable];\n    EXCEPT\n       select TOP(@rowNumber - 1) * from [dbo].[someTable];  \n\n       SET @constVar = @constVar - 1;    \nEND;\n</code></pre>\n'}, {'answer_id': 59001597, 'author': 'Mashood Murtaza', 'author_id': 11537677, 'author_profile': 'https://Stackoverflow.com/users/11537677', 'pm_score': 0, 'selected': False, 'text': '<p>Most suitable answer I have seen on <a href="https://www.codeproject.com/Questions/219741/Select-nth-row-of-a-table-in-sql-server" rel="nofollow noreferrer">this</a> article for sql server</p>\n\n<pre><code>WITH myTableWithRows AS (\n    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*\n    FROM myTable)\nSELECT * FROM myTableWithRows WHERE row = 3\n</code></pre>\n'}, {'answer_id': 59179791, 'author': 'FoxArc', 'author_id': 3866382, 'author_profile': 'https://Stackoverflow.com/users/3866382', 'pm_score': 0, 'selected': False, 'text': '<p>If you want to look at native functionalities: \nMySQL, PostgreSQL, SQLite, and Oracle (<strong>basically SQL Server doesn\'t seem to have this function</strong>) you could ACTUALLY use the NTH_VALUE window function.\nOracle Source: <a href="https://www.techonthenet.com/oracle/functions/nth_value.php" rel="nofollow noreferrer">Oracle Functions: NTH_VALUE</a></p>\n\n<p>I\'ve actually experimented with this in our Oracle DB to do some comparing of the first row (after ordering) to the second row (again, after ordering).\nThe code would look similar to this (in case you don\'t want to go to the link):</p>\n\n<pre><code>SELECT DISTINCT dept_id\n     , NTH_VALUE(salary,2) OVER (PARTITION BY dept_id ORDER BY salary DESC\n           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) \n        AS "SECOND HIGHEST"\n     , NTH_VALUE(salary,3) OVER (PARTITION BY dept_id ORDER BY salary DESC\n           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\n        AS "THIRD HIGHEST"\n  FROM employees\n WHERE dept_id in (10,20)\n ORDER \n    BY dept_id;\n</code></pre>\n\n<p>I\'ve found it quite interesting and I wish they\'d let me use it.</p>\n'}, {'answer_id': 64932564, 'author': '3rdRockSoftware', 'author_id': 12927325, 'author_profile': 'https://Stackoverflow.com/users/12927325', 'pm_score': 1, 'selected': False, 'text': "<p>I'm a bit late to the party here but I have done this without the need for windowing or using</p>\n<pre><code>WHERE x IN (...)\n</code></pre>\n<pre><code>SELECT TOP 1\n--select the value needed from t1\n[col2]\nFROM\n(\n   SELECT TOP 2 --the Nth row, alter this to taste\n   UE2.[col1],\n   UE2.[col2],\n   UE2.[date],\n   UE2.[time],\n   UE2.[UID]\n   FROM\n   [table1] AS UE2\n   WHERE\n   UE2.[col1] = ID --this is a subquery \n   AND\n   UE2.[col2] IS NOT NULL\n   ORDER BY\n   UE2.[date] DESC, UE2.[time] DESC --sorting by date and time newest first\n) AS t1\nORDER BY t1.[date] ASC, t1.[time] ASC --this reverses the order of the sort in t1\n</code></pre>\n<p>It seems to work fairly fast although to be fair I only have around 500 rows of data</p>\n<p>This works in MSSQL</p>\n"}, {'answer_id': 64995930, 'author': 'vinaych', 'author_id': 3802476, 'author_profile': 'https://Stackoverflow.com/users/3802476', 'pm_score': 0, 'selected': False, 'text': '<pre><code>WITH r AS (\n  SELECT TOP 1000 * FROM emp\n)\nSELECT * FROM r\nEXCEPT\nSELECT TOP 999 FROM r\n</code></pre>\n<p>This will give the 1000th row in SQL Server.</p>\n'}]
['https://Stackoverflow.com/questions/16568', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1944/']