Search This Blog

Friday, November 29, 2013

Finding the Nth highest value in a table

I recently was asked by a colleague about finding the  Nth highest value in a table. For example if there is an employee table then finding the 10th highest salary or something similar.


I thought about it and created a query below which obviously is not showing anyone’s salary :) but shows how we can implement it.


Enjoy! and please comment.


declare @nth smallint


set @nth = 4


if (@nth <= 0)

begin

– default to 1 if inappropriate value is provided

set @nth = 1

end


set nocount on;


declare @t table (Id int identity(1, 1), Value decimal(18, 3))

insert into @t (Value) values (1234.56)

insert into @t (Value) values (5678.90)

insert into @t (Value) values (2345.67)

insert into @t (Value) values (3456.78)

insert into @t (Value) values (6789.10)

insert into @t (Value) values (7890.12)

insert into @t (Value) values (8912.34)

insert into @t (Value) values (4567.89)


select [Value] = max([t1].[Value])

from @t [t1]

left join (select top (@nth – 1) [Value]

from @t

order by value desc) [t2] on [t2].[Value] = [t1].[Value]

where [t2].[Value] is null


select [OrderId] = ROW_NUMBER() over (order by value desc)

, [Value] = value

from @t


set nocount off



Finding the Nth highest value in a table

Friday, November 8, 2013

Experience with MS SQL Server

After years of experience with SQL server, I have gathered some of the practices that are good for writing SQL queries, procedures and functions.

Here is my work as presentation, enjoy!!

Monday, October 7, 2013

Devil's Circuit

On 6th October 2013, Delhi region saw and experienced Devil’s Circuit, India’s First Obstacle Run Non Competitive Series. People looking for some adventure and fun, wanted to spend their Sunday in a different way soaked themselves up in mud, freezing water, climbed ropes and walls.. yesterday was for them!!! The day saw participants from various walks of life, students, young professionals, foreign nationals, families…just about everyone!!! The spirit of the participants was amazing!! After the race all the participants who completed the race got a beer(for refreshing yourself :) ), Devil’s T-Shirt and an energy drink and of course a medal upon completion. Live music playing throughout the circuit kept the crowd and participants cheered up!!


You could google for pictures of the event and get prepared for the next Devil’s Circuit. For those of you who missed participating in this one, there’s another one scheduled in March 2014 in NCR, followed by next one in Pune, then Hyderabad and finally in Bangalore!!!


Go ahead and have some fun….



Devil's Circuit

Wednesday, October 2, 2013

SQL Server Fun - Averages with Rollup

Here is tsql way of getting averages and also rolling up. Below example is using adventureworks database that is available with SQL server. It is getting average pay rate by each department and also overall by the company.


use [AdventureWorks]

select[Department] = case when grouping(d.GroupName) = 1 then ‘Company Average’

                              else d.GroupName end,

            [Average Pay Rate] = avg(p.Rate)

from (select [EmployeeID], [Rate] = max([Rate])

            from [HumanResources].[EmployeePayHistory]

            group by [EmployeeID]) p

      inner join [HumanResources].[EmployeeDepartmentHistory] h

                  on h.[EmployeeID] = p.[EmployeeID] and h.[EndDate] is null

      inner join [HumanResources].[Department] d on d.[DepartmentID] = h.[DepartmentID]

group by d.[GroupName]

with rollup

order by 1


SQL Server Fun - Averages with Rollup

Monday, September 30, 2013

My PMI 2013 conference experience – Healthcare in India

I recently attended the PMI 2013 National Conference hosted at “The Leela” Gurgaon. It was indeed a grand event that I enjoyed thoroughly. I was particularly intrigued by the presentation in the field of Healthcare in India from the Fortis group CEO Vishal Bali. Having the background in the field and experienced in the international market at the USA, I am really excited about the opportunities in this Sector in India.


Healthcare is directly or indirectly linked with the economic growth of the country, while it directly produces opportunity for healthcare professionals, it indirectly creates lot of opportunity in other fields like management, IT etc. Not only that, it helps in increasing the life expectancy thereby the overall working task force in the economy. While that is for the global vision, I am concerned about specifics in the healthcare industry in India. There is a lot to be done and opportunity to learn from the systems in other countries – what was done right and what can be done better.


I am particularly interested in hearing from the industry experts about topics like – Preventive care, Diagnosis standardization, Patient centric approach with information sharing and the most talked about topic for all insurance companies – fraud prevention.


These are some of the burning topics especially in healthcare in India.  I have had some experience of working in these areas and seen both the good and the bad of systems.


Looking forward to hear from some of the experts….



My PMI 2013 conference experience – Healthcare in India

Wednesday, September 25, 2013

Brain teaser series....Candy Jar

Find the right candy jar…


Who does not love candy? I always loved those colorful candies but I liked that one colored candy. Now I got red candy and blue candy but I like to get the red one, here is my trouble. There are 3 jars (not transparent) that are mislabeled, one jar has red candy, one has blue candy and one has a mix. Now the mix is not necessarily 50-50, it could be any ratio. I have to blindly pick candy from the jars and find out which jar has which type of candy in the minimal possible turns. How many turns are needed to find out which Jar has what candy?


Did you know that red jelly beans are kid’s favorite. Have fun :)


Brain teaser series....Candy Jar