select n rows in sql

I have a table

Country | Capital
----------------------
France  |  Paris
Germany | Berlin
USA     | Washin开发者_StackOverflow中文版gton
Russia  | Moscow.

I need to select all rows except the first one.The table is having no primary key. How should i do this?

SELECT *
FROM (
   SELECT country, capitol, rownum as rn
   FROM your_table
   ORDER BY country
) 
WHERE rn > 1

If the "first one" is not defined through sorting by country, then you need to apply a different ORDER BY in the inner query.

Edit

For completeness, the ANSI SQL solution to this would be:

SELECT *
FROM (
   SELECT country, 
          capitol, 
          row_number() over (order by country) as rn
   FROM your_table
) 
WHERE rn > 1

That is a portable solution that works on almost all major DBMS

The way to do it with Oracle is the following:

SELECT country, capital FROM 
       ( SELECT rownum rn, country, capital
           FROM table
       )
 WHERE rn > 1

You cannot put a direct >N condition on rownum, because ROWNUMs are assigned when rows are fetched and your condition will never evaluate to TRUE.

Alternative is:

SELECT country, capital FROM table
 MINUS
SELECT country, capital FROM table WHERE rownum <= 1