October 1, 2013 at 12:10 pm
Hi,
I need to get column from JOINed table but its one to many so I need to select only single instance for c2 , to keep original number of records, I tried to do select top 1 like below but this suddenly gave me very very bad performance, is there any other good way to do this? is there any way to avoid corr query?
-- select 100 pk, 'Alpha' c1 into #temp -- drop table #temp
;with t as (
select 100 pk, 1 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 3 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 1 level, 'Alpha' c1, 'ID102' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID102' c2
)
select #temp.pk, #temp.c1 from #temp
join t on t.pk = #temp.pk
where t.level = 1
AND t.c2 = (select top 1 tt.c2 from t tt where t.pk = tt.pk)
Tx
Mario
October 1, 2013 at 12:27 pm
mario17 (10/1/2013)
Hi,I need to get column from JOINed table but its one to many so I need to select only single instance for c2 , to keep original number of records, I tried to do select top 1 like below but this suddenly gave me very very bad performance, is there any other good way to do this? is there any way to avoid corr query?
-- select 100 pk, 'Alpha' c1 into #temp -- drop table #temp
;with t as (
select 100 pk, 1 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 3 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 1 level, 'Alpha' c1, 'ID102' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID102' c2
)
select #temp.pk, #temp.c1 from #temp
join t on t.pk = #temp.pk
where t.level = 1
AND t.c2 = (select top 1 tt.c2 from t tt where t.pk = tt.pk)
Tx
Mario
Not a lot of details here to provide a very solid answer. If there are multiple rows in t how do you decide which one to get?
Maybe as simple as this?
select top 1 #temp.pk, #temp.c1 from #temp
join t on t.pk = #temp.pk
where t.level = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2013 at 12:48 pm
mario17 (10/1/2013)
AND t.c2 = (select top 1 tt.c2 from t tt where t.pk = tt.pk)
Hi
The clause above is a bit of a problem. In the case of the example, you are joining two rows from t back onto all five rows in t to get back a unsorted value for c2. So in this case it could be either ID101 or ID102. Do you mind which?
This may help, note the order by in the cross apply
select a.pk, a.c1, b.c2
from #temp a
cross apply (
SELECT TOP 1 c2
FROM t
WHERE t.pk = a.pk and t.level = 1
ORDER BY c2
) b;
October 1, 2013 at 1:59 pm
Thank CCChamp,
I can't go with top on main Select because I have a big list for Alpha, Bravo, Chrlie,.... Meanhile I change top 1 for max() and it kind of solved my problem, I didn't go into table ddl details to see what going on there.
Tx
M
October 1, 2013 at 6:36 pm
Perhaps like this?
with t as (
select 100 pk, 1 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 3 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 1 level, 'Alpha' c1, 'ID102' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID102' c2
)
SELECT a.pk, a.c1
FROM #temp a
JOIN
(
SELECT *,rn=ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY (SELECT NULL))
FROM t
) b ON b.[level]=1 AND b.pk = a.pk
WHERE rn=1;
Because I don't see you returning any columns from the right table, I assume that the only reason you're doing a JOIN at all is to remove rows (c1) from the left table that aren't in the right table?
If that is true, this maybe all you need.
with t as (
select 100 pk, 1 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 3 level, 'Alpha' c1, 'ID101' c2 union
select 100 pk, 1 level, 'Alpha' c1, 'ID102' c2 union
select 100 pk, 2 level, 'Alpha' c1, 'ID102' c2
)
SELECT pk, c1
FROM #temp a
WHERE EXISTS
(
SELECT 1
FROM t
WHERE t.pk = a.pk AND t.c1 = a.c1
)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply