1
SELECT * FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS rn, p.*
  FROM(
    SELECT * FROM products
  ) p) p2
WHERE rn BETWEEN 0 AND 10
ORDER BY rn

I don't want the above query to return the rn column.

Max Toro
  • 28,282
  • 11
  • 76
  • 114
  • 4
    Sure. Replace `SELECT * FROM` with the actual columns – juergen d Dec 09 '13 at 20:21
  • Can't you look up the columns of the `products` table in your DB? `SELECT * FROM products` returns that. – juergen d Dec 09 '13 at 20:32
  • They are everything in your query results except rn. – Dan Bracuk Dec 09 '13 at 20:32
  • The subquery `SELECT * FROM products` can be anything, that is just an example, and it's out of my control. Imagine the most crazy query you can think of, now put it there. I don't know the columns used. – Max Toro Dec 09 '13 at 20:39
  • 1
    No this isn't possible. You must either specify the desired columns explicitly or use `*` and get the extra column. There is no `* - RN` syntax. – Martin Smith Dec 09 '13 at 21:16

1 Answers1

-1

As of your sample query you are selecting top 10 records ordered by ProductID. If it is true then the same results can be achieved by the following:

select * from
(select * from products order by ProductID)
where rownum <= 10

If it is not the results you are expecting then please correct your query (maybe add "PARTITION BY").

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29