select 2nd highest value per group 1

[ethorn10]

Hello all...

I have read up on ways to achieve part of my subject - 2nd highest values -

select min(field) from table where id in (select top 2 id from table order by field desc)

However, mine is only slightly different yet it's killing me. I need the 2nd highest value for each group that is returned. For instance, the following data:

level  |  group
-----------------
  1    |    1
  6    |    1
  1    |    2
  6    |    2
  1    |    3
  6    |    4
  1    |    5
  1    |    6
  3    |    6
  6    |    6
  1    |    7
  6    |    8

Should return:

level  |  group
-----------------
  1    |    1
  1    |    2
  1    |    3
  6    |    4
  1    |    5
  3    |    6
  1    |    7
  6    |    8

Any help will be greatly appreciated. Thanks.

 

[gmmastros]

Since you are looking for the second highest value, I would first put the data in to a table variable with an identity column, and then you can do a self join to get the second highest value. Something like this...

[green]-- Dummy data[/green]
Declare @Temp Table(Level Integer, GroupNum Integer)

Insert Into @Temp Values(  1    ,    1)
Insert Into @Temp Values(  6    ,    1)
Insert Into @Temp Values(  1    ,    2)
Insert Into @Temp Values(  6    ,    2)
Insert Into @Temp Values(  1    ,    3)
Insert Into @Temp Values(  6    ,    4)
Insert Into @Temp Values(  1    ,    5)
Insert Into @Temp Values(  1    ,    6)
Insert Into @Temp Values(  3    ,    6)
Insert Into @Temp Values(  6    ,    6)
Insert Into @Temp Values(  1    ,    7)
Insert Into @Temp Values(  6    ,    8)

[green]-- The query[/green]

Declare @Data Table(RowId Integer Identity(1,1), Level Integer, GroupNum Integer)

Insert Into @Data(Level, GroupNum)
Select Level, GroupNum 
From   @Temp
Order By GroupNum, Level DESC

Select A.GroupNum,
       Max(Coalesce(B.Level, A.Level)) As Level
From   @Data A 
       Left Join @Data B 
         On  A.RowId = B.RowId - 1 
         And A.GroupNum = B.GroupNum
Group By A.GroupNum

Note the use of coalesce. This ensures that when there is a single record for a group, that the value will still be returned.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ethorn10]

Thanks gmmastros! I managed to solve this between the time I posted and the time I checked back <after many headaches> using an almost identical query. However, I used a case statement checking for nulls but I'll be changing it to coalesce as soon as I get back to work (knew about coalesce and for some reason completely forgot about it).

a star nonetheless...

 

[gmmastros]

Good. I'll bet you learned a little more because of solving it for yourself, so... congrats.

You'll notice that I posted this solution 2 hours ago. If you click the Click here to mark this thread for e-mail notification link, you would have gotten an email within minutes of my posting and could probably have saved yourself some headaches.

Still, I'm glad you got it working.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ethorn10]

Unfortunately, I can't check email at work...otherwise I would have. Thanks again though.