How to select one record from a SELECT query in MySQL

Question

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.

why not just `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. — Marc B, Dec 18 '13 at 15:21
Thanks Marc B. LIMIT 3 was a nice option. But how do I construct a query to INSERT into 3 different fields as mentioned above after executing this SELECT query. I am doing this in script. Thanks for your help tho. — Manus, Dec 18 '13 at 15:24
Got the answer here http://stackoverflow.com/questions/9422529/mysql-how-do-you-insert-into-a-table-with-a-select-subquery-returning-multiple-r — Manus, Dec 18 '13 at 15:27
So I tried INSERT INTO TABLE_1(TOP_SALES_COUNTRY,SECOND_TOP_SALES_COUNTRY,THIRD_TOP_SALES_COUNTRY) VALUES (SELECT COUNTRY FROM COUNTRIES WHERE USERTYPE = 'PREMIUM' ORDER BY SALES DESC LIMIT 3) and it didn't work. Kindly advise — Manus, Dec 18 '13 at 16:09

score 1 · Answer 1 · answered Dec 18 '13 at 15:21

1

SELECT * FROM COUNTRIES WHERE USERTYPE = 'PREMIUM' ORDER BY SALES DESC limit 0,1; to get the first row

answered Dec 18 '13 at 15:21

reverse_engineer

4,239
4
18
27

1

how does this retrieve the top 3 rows ? – BWS Dec 18 '13 at 15:24
@BWS it doesn't, it retrieves the top row, obvious changes needed to retrieve the top 3 – reverse_engineer Dec 18 '13 at 15:34

score 1 · Answer 2 · answered Dec 18 '13 at 16:12

1

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.

answered Dec 18 '13 at 16:12

Manus

869
2
10
20

I have added some more ways to do it. – Sahil Sareen Dec 18 '13 at 16:50

score 0 · Accepted Answer · answered Dec 18 '13 at 16:38

0

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;

answered Dec 18 '13 at 16:38

Sahil Sareen

1,813
3
25
40

How to select one record from a SELECT query in MySQL

3 Answers3