I am running the follwoing SQL query:
SELECT * FROM COUNTRIES WHERE USERTYPE = 'PREMIUM' ORDER BY SALES DESC;
The output of this query yields a list of countries.
Now I need to populate a field in another table which is like TOP_SALES_COUNTRY, SECOND_TOP_SALES_COUNTRY and THRID_TOP_SALES_COUNTRY for which I only need the first,second and third records in the output of this SELECT statement. Kindly advise on how this can be achieved.
SELECT * FROM COUNTRIES WHERE USERTYPE = 'PREMIUM' ORDER BY SALES DESC limit 0,1; to get the first row
What worked for me is a modification of reverse_engineer's answer. FOr example to get 2nd top most country i used LIMIT 1,1 and for third value I used LIMIT 2,1. Thank you for your help. Immensely grateful to all.
You could use any of the following:
Option 1:
SELECT TOP number|percent column_name(s)
FROM table_name;
Option 2:
SELECT column_name(s)
FROM table_name
LIMIT number;
Option 3:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
SELECT ... LIMIT 3? You can't really collapse a 3 row result into a 3 column 1 row insert easily. but doing the limit query, fetching the results, then doing a 3-field insert would do the trick.