Page 1 of 1

Select Records Based on Calculation or Value in MySQL

Posted: Tue Aug 17, 2010 7:34 am
by devarishi
Hi,


Today in an interview I was given this problem statement to solve:

MySQL:

There is a column, say "Salary", in a table. The data type of the column "Salary" is, off course, number. Fetch the record with the 5th Highest Salary.

Table may look like this:

Code: Select all

Sr_No	Salary
1	5000
2	7500
3	20000
4	22500
5	25000
6	27500
7	5000
8	7500
9	15000
10	10000
So the output of our query, as we can see, should be:

Code: Select all

9	15000



I would like to add one problem here:

How to fetch top 5 records, salary-wise, from the above table?

Re: Select Records Based on Calculation or Value in MySQL

Posted: Tue Aug 17, 2010 10:29 am
by mikosiko

Re: Select Records Based on Calculation or Value in MySQL

Posted: Tue Aug 17, 2010 10:30 am
by AbraCadaver
[text]ORDER BY Salary DESC LIMIT 5[/text]

Re: Select Records Based on Calculation or Value in MySQL

Posted: Tue Aug 17, 2010 10:40 am
by mikosiko
AbraCadaver wrote:[text]ORDER BY Salary DESC LIMIT 5[/text]
that solve the second question... let him figure out the first one :wink:

Re: Select Records Based on Calculation or Value in MySQL

Posted: Wed Aug 25, 2010 5:17 pm
by devarishi
mikosiko wrote:
AbraCadaver wrote:[text]ORDER BY Salary DESC LIMIT 5[/text]
that solve the second question... let him figure out the first one :wink:

Okay, I have figured it out:

Code: Select all

mysql> select * from book;
+------+--------------+------+
| ISBN | title        | mrp  |
+------+--------------+------+
|  111 | C            |  500 |
|  112 | VC++         |  200 |
|  114 | VB           |  100 |
|  115 | C++          |  100 |
|  116 | PHP          |  100 |
|  117 | C            |  100 |
|  118 | VB           |  100 |
|  119 | c            |  200 |
|  120 | C#           |  200 |
|  121 | SQL          |  350 |
|  122 | MySQL        |  350 |
|  123 | Linux Apache |  350 |
|  124 | UNIX & Linux |  350 |
+------+--------------+------+
13 rows in set (0.00 sec)
And here is the top 5th record:

[syntax]
mysql> select * from book order by mrp desc limit 4,1;
+------+--------------+------+
| ISBN | title | mrp |
+------+--------------+------+
| 124 | UNIX & Linux | 350 |
+------+--------------+------+
1 row in set (0.00 sec)

mysql>[/syntax]


Thanks for the hinting at the

Code: Select all

LIMIT 
clause.