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 2005 Forums

Transact-SQL (2005)

2nd maximum price of each group

Author

Topic

hai_venkat
Starting Member

10 Posts

Posted - 2010-11-22 : 09:33:48

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

Venkat

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 10:07:20

I think without row_number() it isn't a simple query but WITH row_number() it would be.
But anyway, here is a solution without:


select
i.itemid,
i.[name],
dt.purchase_price
from item as i
join
(
Select itemid,purchase_price From purchase E1 Where
    (2-1) = (Select Count(Distinct(E2.purchase_price)) From purchase E2 Where
		E2.purchase_price > E1.purchase_price
		and E2.itemid = E1.itemid)
union all
Select itemid,max(purchase_price) as purchase_price From purchase group by itemid having count(*) = 1
) as dt
on dt.itemid = i.itemid

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 10:09:17

inspired by http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 09:57:04

quote:
Originally posted by hai_venkat

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

Venkat

Venkat

is row_number a complex function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-11-24 : 10:51:02

quote:
Originally posted by visakh16

quote:
Originally posted by hai_venkat

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

Venkat

Venkat

is row_number a complex function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No maybe he wants to "test" on how stupid or smart we are..

PBUH

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-24 : 20:55:16

By setting the criteria of a "Simple Query", you are actually complicating this for yourself.

I would use this query


Select a.itemid,a.name,MAX(purchase_price) as purchase_price
from
item a
inner join
(
Select ROW_NUMBER() over (partition by a.itemid order by a.purchase_price) as RowID,*
from 
	Purchase a
 ) b
 on a.itemid = b.itemid 
 where b.RowID <=2 
 group by a.itemid,a.name
order by itemid

Here is the simplest option without a "Row_Number"


Select a.itemid,a.name,MAX(b.purchase_price) as purchase_price
from
item a
Cross apply
(
Select top 2 *
from 
	Purchase aa
where aa.itemid = a.itemid 
order by aa.purchase_price 
 ) b
 group by a.itemid,a.name
order by itemid

And here is the simplest way I can think off without any available functions besides standard simple queries.


Select a.itemid,a.name
,(Select top 1 Purchase_price from (Select top 2 Purchase_price from Purchase aaa where aaa.itemid = a.itemid order by purchase_price asc) aa order by purchase_price desc)
from
item a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-26 : 01:34:37

Hi,
This will also satisfy most of the part

select p.itemid,MAX(p.purchase_price)
from
purchase p
join
(
select p.itemid,[FirstMaxPrice]=Max(p.purchase_price),[ItemCount]=COUNT(1)
from
Purchase p
group by p.itemid
)p1
on p.itemid = p1.itemid and (p.purchase_price <>p1.FirstMaxPrice or p1.ItemCount = 1)
group by p.itemid

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-26 : 01:55:14

quote:
Originally posted by Vinnie881

By setting the criteria of a "Simple Query", you are actually complicating this for yourself.

I would use this query

Select a.itemid,a.name,MAX(purchase_price) as purchase_price
from
item a
inner join
(
Select ROW_NUMBER() over (partition by a.itemid order by a.purchase_price) as RowID,*
from
Purchase a
) b
on a.itemid = b.itemid
where b.RowID <=2
group by a.itemid,a.name
order by itemid

Here is the simplest option without a "Row_Number"

Select a.itemid,a.name,MAX(b.purchase_price) as purchase_price
from
item a
Cross apply
(
Select top 2 *
from
Purchase aa
where aa.itemid = a.itemid
order by aa.purchase_price
) b
group by a.itemid,a.name
order by itemid

And here is the simplest way I can think off without any available functions besides standard simple queries.

Select a.itemid,a.name
,(Select top 1 Purchase_price from (Select top 2 Purchase_price from Purchase aaa where aaa.itemid = a.itemid order by purchase_price asc) aa order by purchase_price desc)
from
item a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-26 : 01:58:00

quote:
Originally posted by Vinnie881

By setting the criteria of a "Simple Query", you are actually complicating this for yourself.

I would use this query

Select a.itemid,a.name,MAX(purchase_price) as purchase_price
from
item a
inner join
(
Select ROW_NUMBER() over (partition by a.itemid order by a.purchase_price) as RowID,*
from
Purchase a
) b
on a.itemid = b.itemid
where b.RowID <=2
group by a.itemid,a.name
order by itemid

Here is the simplest option without a "Row_Number"

Select a.itemid,a.name,MAX(b.purchase_price) as purchase_price
from
item a
Cross apply
(
Select top 2 *
from
Purchase aa
where aa.itemid = a.itemid
order by aa.purchase_price
) b
group by a.itemid,a.name
order by itemid

And here is the simplest way I can think off without any available functions besides standard simple queries.

Select a.itemid,a.name
,(Select top 1 Purchase_price from (Select top 2 Purchase_price from Purchase aaa where aaa.itemid = a.itemid order by purchase_price asc) aa order by purchase_price desc)
from
item a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Good solution.

Iam a slow walker but i never walk back

hai_venkat
Starting Member

10 Posts

Posted - 2010-11-26 : 02:09:20

Thank You all for your replies...
Dinesh you rocks...

Venkat

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-26 : 03:11:00

I modified these queries to correct a issue.


Select a.itemid,a.name,Min(purchase_price) as purchase_price
from
item a
inner join
(
Select ROW_NUMBER() over (partition by a.itemid order by a.purchase_price desc) as RowID,*
from 
	Purchase a
 ) b
 on a.itemid = b.itemid 
 where b.RowID <=2 
 group by a.itemid,a.name
order by itemid  


Select a.itemid,a.name,Min(b.purchase_price) as purchase_price
from
item a
Cross apply
(
Select top 2 *
from 
	Purchase aa
where aa.itemid = a.itemid 
order by aa.purchase_price desc
 ) b
 group by a.itemid,a.name
order by itemid


Select a.itemid,a.name
,(Select top 1 Purchase_price from (Select top 2 Purchase_price from Purchase aaa where aaa.itemid = a.itemid order by purchase_price desc) aa order by purchase_price asc)
from
item a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

hai_venkat
Starting Member

10 Posts

Posted - 2010-11-26 : 04:08:36

Thanks Vinnie...
It helps me lot...
Keep it up.. gud work:-)

Venkat

Sachin.Nand

2937 Posts

Posted - 2010-11-26 : 13:59:33

quote:
Originally posted by hai_venkat

Thanks Vinnie...
It helps me lot...
Keep it up.. gud work:-)

Venkat

I wonder how do all those queries qualify to be a "simple" query in your words?

PBUH

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-26 : 20:36:17

Here is a modification to Denesh's query(I personally don't like using an OR on a where clause when I don't need to). Doesn't make the other approach wrong, here is just another way to use that method without needing a "or" in the where clause.



select p.itemid,MAX(case when p.purchase_price <> p1.firstmaxprice then p.purchase_price 
					when p1.itemcount = 1 then p.purchase_price else  null end) 
from 
purchase p
join
(
select p.itemid,Max(p.purchase_price) as firstmaxprice,COUNT(*)as itemcount
from
Purchase p
group by p.itemid
)p1
on p.itemid = p1.itemid 
group by p.itemid

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

MohanKandasamy
Starting Member

9 Posts

Posted - 2010-11-27 : 19:20:42

Hi,

Sugesstion, instead of count (*) , you can use count(0) for performance.

Mohan Kandasamy

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-27 : 20:41:32

quote:
Originally posted by MohanKandasamy

Hi,

Sugesstion, instead of count (*) , you can use count(0) for performance.

Mohan Kandasamy

Count(*),count(1),count(0),Etc, should all have virtually the same performance. I would like to see an article or information that states otherwise.

At one point in time count(*) would be treated differantly than a count(1) or a count(0), but in all recent releases of SQL, and according to microsoft count(*) is the recommended method.

Please provide your source that indicates one method increases performance.

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 00:29:30

quote:
Originally posted by hai_venkat

Thank You all for your replies...
Dinesh you rocks...

Venkat

Welcome

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 01:28:35

quote:
Originally posted by Vinnie881

Here is a modification to Denesh's query(I personally don't like using an OR on a where clause when I don't need to). Doesn't make the other approach wrong, here is just another way to use that method without needing a "or" in the where clause.

select p.itemid,MAX(case when p.purchase_price <> p1.firstmaxprice then p.purchase_price
when p1.itemcount = 1 then p.purchase_price else null end)
from
purchase p
join
(
select p.itemid,Max(p.purchase_price) as firstmaxprice,COUNT(*)as itemcount
from
Purchase p
group by p.itemid
)p1
on p.itemid = p1.itemid
group by p.itemid

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Thanks

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources