Select Records Based on Calculation or Value in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

Select Records Based on Calculation or Value in MySQL

Post 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?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select Records Based on Calculation or Value in MySQL

Post by mikosiko »

User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Select Records Based on Calculation or Value in MySQL

Post by AbraCadaver »

[text]ORDER BY Salary DESC LIMIT 5[/text]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select Records Based on Calculation or Value in MySQL

Post 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:
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

Re: Select Records Based on Calculation or Value in MySQL

Post 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.
Post Reply