September 30, 2005 at 2:42 pm
Have a table with following columns: source, sourcenumber, product, productnumber. Need to select top 10 sources (using sourcenumber) and for each source- top 5 products (using productnumber).
Thanks
September 30, 2005 at 3:50 pm
Use derived table something like this.
by top meaning the first 10, or the top ten ranked by sales or some other method?
Select top 5 Product, ProductNumber, SourceNumber, Source
From Products
Join (Select top 10 SourceNumber, Source
from Sources) derivedTable on DerivedTable.SourceNumber = Products.SourceNumber
September 30, 2005 at 3:58 pm
There not enough information to determine exactly what is needed. Can you post the primary key of the table ?
Assummimg you want the top 10 Sources and then the top 5 products within each source, try the below SQL. Note that if any of the top 10 sources do have have 5 products, the total number of rows will be less than 50. Note also that the Soruce Rank indicates the number of sources with lower values for source number and similarly for products within sources.
Create table Foo
( sourcevarchar(255)not null
, sourcenumberinteger not null
, productvarchar(255)not null
, productnumber integernot null
, primary key (source, product)
)
go
insert into Foo
(source, sourcenumber, product, productnumber)
select t.name, t.id, i.name, i.indid
from master.dbo.sysobjects t
join master.dbo.sysindexes i
on t.id = i.id
go
select SourceTop10.SourceRank + 1
, SourceProductTop5.SourceProductRank
,SourceTop10.Source
, SourceProductTop5.product
FROM(
select S.Source, count(SLower.SourceNumber)
from (select distinct source, sourcenumber from Foo ) as S
LEFT OUTER join (select distinct source, sourcenumber from Foo ) as SLower
on SLower.SourceNumber (select count(distinct source) - 11 from foo )
) As SourceTop10 ( Source, SourceRank )
JOIN(select Foo.Source, Foo.product, count(Foo.ProductNumber)
from Foo
left outer join Foo as FooLower
on FooLower.Source= Foo.Source
and FooLower.productnumber <= Foo.ProductNumber
group by Foo.Source, Foo.product
having count(Foo.ProductNumber) < 5
) as SourceProductTop5 (Source, product , SourceProductRank)
on SourceProductTop5.Source= SourceTop10.Source
order by SourceTop10.SourceRank desc
, SourceProductTop5.SourceProductRank desc
SQL = Scarcely Qualifies as a Language
September 30, 2005 at 9:17 pm
Thanks for your replies.
Ray: probably I wasn't clear. I need top 10 sources with biggest sourcenumbers (actually, it's score for every source) and then for each source from this 10 ones select 5 products with biggest productnumber (it's also score but for product).
Carl: there is no primary key (actually it's table variable I've created in SP). I didn't get (may be need some more time to understand your query logic) what is the reason for Foo table comprises sysobjects and sysindex?
Thanks
October 3, 2005 at 9:36 am
Can you post a sample tables ,
Some sample data,
and a query on how you figure what the top 10 sources are?
and a sample result set of how you want the output to look.
http://www.aspfaq.com/etiquette.asp?id=5006
Help us help U
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply