Retreive first three maximum values

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

Transact-SQL (2000)

Retreive first three maximum values

Author

Topic

June
Starting Member

18 Posts

Posted - 2009-03-25 : 02:27:00

Hi,

I have a table IssueDetails which contains the issuing details of materials.

IssueDate MaterialCode IssueQuantity Rate

Now I need to retreive the top three maximum issued materials for a year. I will first sum the total IssueQuantity for each material in that year. Then I will retreive the top three rows. Is this approach fine? Looking for any alternative approaches. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 03:30:19

Do you want to have top 3 for each year?
Try this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

June
Starting Member

18 Posts

Posted - 2009-03-25 : 05:07:30

quote:
Originally posted by madhivanan

Do you want to have top 3 for each year?
Try this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Not exactly. Before taking the top 3 for each year, I need to calculate the SUM(IssueQuantity) for every material. What I am asking is whether I can do both in a single query. Perhaps a subquery will do.

souLTower
Starting Member

39 Posts

Posted - 2009-03-25 : 12:06:15

This is a terrible way to do this because of the row by row RBAR comparison but it was the only way I could figure out (in between real work tasks) :)


set nocount on

declare @tbl table (materialName varchar(100), YR int, qty int)

insert into @tbl (materialName, YR, qty)
select 'stuff', 2005, 10 union all
select '1stuff', 2005, 10 union all
select '1stuff', 2005, 10 union all
select '2stuff', 2005, 10 union all
select '2stuff', 2005, 10 union all
select '2stuff', 2005, 10 union all
select '3stuff', 2005, 10 union all
select '3stuff', 2005, 10 union all
select '3stuff', 2005, 10 union all
select '3stuff', 2005, 10 union all

select 'stuff', 2006, 10 union all
select '1stuff', 2006, 10 union all
select '1stuff', 2006, 10 union all
select '2stuff', 2006, 10 union all
select '2stuff', 2006, 10 union all
select '2stuff', 2006, 10 union all
select '3stuff', 2006, 10 union all
select '3stuff', 2006, 10 union all
select '3stuff', 2006, 10 union all
select '3stuff', 2006, 10 union all
select '4stuff', 2006, 10 union all
select '4stuff', 2006, 10 union all
select '4stuff', 2006, 10 union all
select '4stuff', 2006, 10 union all
select '4stuff', 2006, 10 union all
select '4stuff', 2006, 10 


-- The query will return the top 2 performers for each fiscal yr

select D.materialName, D.yr, D.sumQty  
from 
(
  select materialName, Yr, sum(qty) as sumQTY from @tbl group by materialName, YR
) D 
where 
(
  select count(X.materialName) from 
  (
    select materialName, Yr, sum(qty) as sumQTY from @tbl group by materialName, YR
  ) X where X.yr = D.yr and X.sumQTY >= D.sumQTY
)  <= 2 
order by D.yr, D.sumQTY desc, D.materialName

God Bless

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources