Today I came to know that we can use CASE with ORDER BY clause. Its a nice functionality which helps you to avoid UNIOUN when you have to display the records in some specific order and still you need to sort them.
As shown below I have created an Employee table with ID (Primary key),Name, Address and Phone columns.
Fig – (1) Employee table.
I have also inserted some temporary data as shown below,
1: INSERT INTO Employee VALUES ('Chirag Darji','Ahmedabad','123456789')
2: INSERT INTO Employee VALUES ('Dipak Patel','USA','123456789')
3: INSERT INTO Employee VALUES ('Shailesh Patel','USA','123456789')
4: INSERT INTO Employee VALUES ('Piyush Vadher','Gujarat','123456789')
5: INSERT INTO Employee VALUES ('Mihir Panchal','Gujarat','123456789')
6: INSERT INTO Employee VALUES ('Vishal Patel','Ahmedabad','123456789')
Fig – (2) Insert Statements
Now consider that have to display the records of employee table order by Address however you want first it should display all the records with USA in address field then Ahmedabad then Gujarat and after that all. Here is the output that we want,
Fig – (3) Result
Below is the query that displays the result as shown in fig – (3),
1: SELECT * FROM Employee ORDER BY
2: CASE WHEN Address = 'USA' THEN 1
3: WHEN Address = 'Ahmedabad' THEN 2
4: WHEN Address = 'Gujarat' THEN 3
5: ELSE 4
Happy Programming !!!
Read Full Post »
During the free time today, I was looking for some articles on ASP.NET internals. I found an awesome article about page life cycle and viewstate. Author has explained a viewstate in very detail and simple manner. You can read the article here.
Read Full Post »