Finding the Nth Highest Salary from table

June 2, 2007

This is the most common question asked in every interview for the database. As I am more familiar with mysql , Its so simple with the following query

SELECT * FROM employee ORDER BY salary DESC LIMIT 2,1

This query will bring the second highest salary from the table employee.

But Query should work with every database so here is the correct query and its details explaination which will bring specified highest salary from the table

select * from employee emp where 3 = (select count(*) from employee emp where a.salary <= b.salary) ;

I made a details comparision set about the operations of query.

Table employee is having the 6 record with salary ranging from 1000 to 6000

Query:
====
SELECT * FROM employee emp WHERE 3 = (SELECT COUNT(*) FROM employee emp WHERE a.salary <= b.salary) ;

Detail Record Condition Check
=====================
1)a <= b
1000<=1000
1000<=2000
1000<=3000
1000<=4000
1000<=5000
1000<=6000
[count=6]

2)a <= b
2000<=1000
2000<=2000
2000<=3000
2000<=4000
2000<=5000
2000<=6000
[count=5]

3)a <= b
3000<=1000
3000<=2000
3000<=3000
3000<=4000
3000<=5000
3000<=6000
[count=4]

4)a <= b
4000<=1000
4000<=2000
4000<=3000
4000<=4000
4000<=5000
4000<=6000
[count=3]

5)a <= b
5000<=1000
5000<=2000
5000<=3000
5000<=4000
5000<=5000
5000<=6000
[count=2]

6)a <= b
6000<=1000
6000<=2000
6000<=3000
6000<=4000
6000<=5000
6000<=6000
[count=1]

Result : 4000 as its count is matching the condition ie. 3


Framework with more features and real usage

February 12, 2007

Months ago when I heard of the term framework which generate Add/Mod/Del functionality, I was quite surprise and realize that how helpful it could be. It will save a lot of hours I spend on the most part, increase efficiency and will be bug free :). After trying with some framework, I found it does the right job but not with style I write the code. Its interanal are too difficult to understand and modifying such code eats up more time than I ever expected. I thought Its better idea to go the way I/we code. Later after thinking for some time on the right concept, its clear many thing and I realizes building framework is not a tough job. In my earlier post I already talked about when I started with it. This time I develop more better version of code.

I used pear DB class for database and Smarty for templating as most of the PHP developers are using it.

What features my frameworks offer this time?

-Generate Class file based on the table Structure.
-Generate Templates for AddUpdate as well as for Listing the records.
-Sorting on the each column field.
-Pagination.
-Server Validation for Unique values.
-Javascript validation for Blank Values currently.
-Generate JS map which we can use for further validation.
-Complete Add/Modify/Delete.
-Debug options which allow development message enable disable.
-Generate Links based on the class available.
-Search on the table field

New Features I will be adding this month
-Basic Ajax Functionality

Framework is flexible enough to add new functinality in code. Even this framework has ability to generate Coldfusion, ASP as well as Java code.

I soon will be submitting it to sourceforge.net and host it for the developers to take a look about what I am talking.

🙂


Something you can always do at database level

January 9, 2007

I found many developers even sometimes I do some code which could be possible at the database level. Why to format date at coding level when DATE_FORMAT function is available.

Developer should be careful enough to identify the cases where they can performes operation at database level. This will help to improve the overall performance and efficiency of your application.

I will be keep adding the tips about the same in this entry

First
=====
If you want to format date by fetching it from database, you can use DATE_FORMAT function.

eg. SELECT DATE_FORMAT(now(), ‘%y’) FROM tableName


MSSQL ISNULL good function to replace null values

December 5, 2006

MSSQL has a nice function to replace null values conditinally

ISNULL(columnName, newValue)

This function requires two parameter columnName and new value to be replaced in place of null.

This function help me alot to solve the problem at database level instead at the coding level.