2nd maximum price of each group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

2nd maximum price of each group

Author

Topic

hai_venkat
Starting Member

10 Posts

Posted - 2010-11-22 : 09:13:22

Hi,
I want to select a second price of the each group. If only one record is available for a particular group then it should return that value as a second maximum.

create table item
(
itemid int,
name varchar(100)
)

create table Purchase
(
itemid int,
purchase_price numeric(28,2)
)

insert into item values (001,'Mouse')
insert into item values (002,'Keyboard')
insert into item values (003,'Monitor')
insert into item values (004,'Pen')

insert into Purchase values (001,1000)
insert into Purchase values (001,1200)
insert into Purchase values (001,1400)

insert into Purchase values (002,800)
insert into Purchase values (002,700)
insert into Purchase values (002,900)

insert into Purchase values (003,15000)
insert into Purchase values (003,14000)
insert into Purchase values (003,12000)

insert into Purchase values (004,300)

select * from item
select * from Purchase

Expected output is :
Mouse - 1200
Keyboard - 800
Monitor - 14000
Pen - 300

Note : I just want a simple query. Dont reply with row_number or rank functions

Venkat

turbo
Starting Member

19 Posts

Posted - 2010-11-22 : 09:22:20

select distinct a.sal from my_table a where @var = (select count (distinct (b.sal)) from my_table b where a.sal < = b.sal)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 10:26:14

duplicate post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153243

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-11-24 : 01:50:17

I just wonder why people come up with this kind of restrictions?


declare @t table 
(
pname varchar(100),
Value int,
orderseq int
)

insert into @t values('product1' , 25 , 0)
insert into @t values('product1' , 50 , 0)
insert into @t values('product1' , 75 ,0)
insert into @t values('product1' , 100 ,0)
insert into @t values('product4' , 50 , 0)
insert into @t values('product4' , 100 , 0)
insert into @t values('product4' , 80 , 0)
insert into @t values('product7' , 25 , 0)
insert into @t values('product8' , 26 ,0) 


select * from @t

declare @value varchar(100),@orderseq varchar(100),@pname varchar(100)


set @orderseq='1'

declare @cnt int=(select COUNT(*)from @t)
;with cte
as
(
    select top (@cnt) * from @t order by pname,Value desc
)

update cte
set orderseq=@orderseq ,
@orderseq=case when @pname=pname then @orderseq+1 else 1 end,@pname=pname 

select * from @t where orderseq=2

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources