Given the following two tables below, I need help in writing a query that would retreive only 3 or less distinct values of BDesc from tbB table for every row found in tbA.
for example:
I expect result to be:
Aid Bdesc
100 1st Desc for 100
100 2nd Desc for 100
100 3rd Desc for 100
200 1st Desc for 200
200 2nd Desc for 200
200 3rd Desc for 200
300 1st Desc for 300
300 2nd Desc for 300
300 3rd Desc for 300
400 1st Desc for 400
500 1st Desc for 500
500 3rd Desc for 500
The tables are:
use tempdb
go
set nocount on
if exists (select name from sysobjects where name = 'TbA')
Drop table TbA
Create Table TbA ( Aid int )
Insert into TbA values(100)
Insert into TbA values(200)
Insert into TbA values(300)
Insert into TbA values(400)
--select * from TbA
if exists (select name from sysobjects where name = 'TbB')
Drop table TbB
Create Table TbB ( Bid int , BDesc varchar(50) )
INSERT INTO TbB Values(100, '1st Desc for 100')
INSERT INTO TbB Values(100, '2nd Desc for 100')
INSERT INTO TbB Values(100, '3rd Desc for 100')
INSERT INTO TbB Values(100, '3rd Desc for 100')
INSERT INTO TbB Values(200, '1st Desc for 200')
INSERT INTO TbB Values(200, '2nd Desc for 200')
INSERT INTO TbB Values(200, '3rd Desc for 200')
INSERT INTO TbB Values(200, '4th Desc for 200')
INSERT INTO TbB Values(200, '1st Desc for 200')
INSERT INTO TbB Values(300, '1st Desc for 300')
INSERT INTO TbB Values(300, '2nd Desc for 300')
INSERT INTO TbB Values(300, '3rd Desc for 300')
INSERT INTO TbB Values(300, '4th Desc for 300')
INSERT INTO TbB Values(400, '1st Desc for 400')
INSERT INTO TbB Values(400, '1st Desc for 400')
INSERT INTO TbB Values(500, '1st Desc for 500')
INSERT INTO TbB Values(500, '1st Desc for 500')
INSERT INTO TbB Values(500, '3rd Desc for 500')
--select * from TbB
Thanks for your help with this...
Here ya go
Code Snippet
selectdistinct TbB.*
from(selectdistinct aid from TbA)as TbA
innerjoin TbB
on TbA.Aid = TbB.Bid
and TbB.BDesc in
(selectdistincttop 3 BDesc from TbB where Bid = TbA.Aid orderby BDesc)
|||Thanks|||How about if I wanted to get the result like this:
ColA ColB
100 1st Descfor 100, 2nd Descfor 100, 3rd Descfor 100
200 1st Descfor 200, 2nd Descfor 200, 3rd Descfor 200
300 1st Descfor 300, 2nd Descfor 300, 3rd Descfor 300
400 1st Descfor 400
500 1st Descfor 500, 3rd Descfor 500
|||hi, you can try using a udfCREATE FUNCTION dbo.GetBDesc
(
@.AID int
)
RETURNS varchar(800)
AS
BEGIN
DECLARE @.BDesc varchar(100)
SET @.BDesc = ''
SELECT
@.BDesc = @.BDesc + BDesc + ','
FROM (SELECT DISTINCT TOP 3 * FROM TbB a WHERE a.BID = @.AID) b
WHERE BID = @.AID
ORDER BY
BDesc
IF @.BDesc <> '' SET @.BDesc = LEFT(@.BDesc, LEN(@.BDesc) - 1)
RETURN @.BDesc
END
GO
select *
, dbo.GetBDesc(AID)
from tba|||
If you use SQL Server 2005 you dont need a function...
Here the sample,
Code Snippet
Create Table #TableA(
Aid int );
Insert into #TableA values(100)
Insert into #TableA values(200)
Insert into #TableA values(300)
Insert into #TableA values(400)
Insert into #TableA values(500)
Create Table #TableB(
Bid int
,BDesc varchar(50)
)
INSERT INTO #TableB Values(100, '1st Desc for 100')
INSERT INTO #TableB Values(100, '2nd Desc for 100')
INSERT INTO #TableB Values(100, '3rd Desc for 100')
INSERT INTO #TableB Values(100, '3rd Desc for 100')
INSERT INTO #TableB Values(200, '1st Desc for 200')
INSERT INTO #TableB Values(200, '2nd Desc for 200')
INSERT INTO #TableB Values(200, '3rd Desc for 200')
INSERT INTO #TableB Values(200, '4th Desc for 200')
INSERT INTO #TableB Values(200, '1st Desc for 200')
INSERT INTO #TableB Values(300, '1st Desc for 300')
INSERT INTO #TableB Values(300, '2nd Desc for 300')
INSERT INTO #TableB Values(300, '3rd Desc for 300')
INSERT INTO #TableB Values(300, '4th Desc for 300')
INSERT INTO #TableB Values(400, '1st Desc for 400')
INSERT INTO #TableB Values(400, '1st Desc for 400')
INSERT INTO #TableB Values(500, '1st Desc for 500')
INSERT INTO #TableB Values(500, '1st Desc for 500')
INSERT INTO #TableB Values(500, '3rd Desc for 500')
;With DistinctData
as
(
Select Distinct A.Aid,B.BDesc from #TableA A Join #TableB B On A.Aid =B.Bid
),
RowData
as
(
Select Aid,Bdesc,Row_Number() Over(Partition By Aid Order By BDesc) RowID From DistinctData
)
/*
Select
Aid,
BDesc
From
RowData
Where
RowID <=3
*/
Select Distinct
Aid
,Substring((Select ',' + BDesc as [text()] From RowData Sub Where Sub.Aid=Main.Aid And Sub.RowId<=3 For XML Path(''), Elements),2,8000) as Descs
From
RowData Main
|||MG,
How about these two queries (both require SQL 2005)
select a.aid, b.bdesc
from tbA a
cross apply
(select distinct top (3) bdesc from tbB b where b.bid = a.aid) b
;
select a.aid as ColA, stuff((select distinct top (3) ', ' + bdesc from tbB b where b.bid = a.aid order by 1 for xml path('')),1,2,'') as ColB
from tbA a
;
The second one puts them into a single column for you.
Rob|||I should have mentioned that this is for SQL 2000 and for an OLTP environment. The procedure processes approx. 20,000 rows and right now it's using cursor logic which is slowing things down, so I was looking for ways to use set based processing. The function idea is good, but again its going to be row by row processing.|||
The function approach should be a significant improvement over any cursor processing.
Is there something we're not understanding about what you want to accomplish?
|||Hi Rhamille Golimlim,
There is an issue when using "order by" during an aggregate concatenation query.
PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location
http://support.microsoft.com/default.aspx/kb/287515
AMB
|||thanks for the tip hunchback. would it still show a different execution plan if we put the order by inside the subquery?|||Hi Rhamille Golimlim,
If you put the "order by" clause inside the derived table, then how are you going to be sure that the result is sorted if the only way to asure a sorted resultset is using the "order by" clause in the statement that pull the data?. It is like sorting inside a view and not using "order by" clause when you pull from the view.
Concatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
|||hi hunchback,cool, would the xml path approach be the best work around for this scenario? or are there other alternatives or tsql hacks?
/rhamille
