June 4, 2009 at 3:55 am
hi
I have a table like this one
create table #temp
(
a int,
b int,
c int,
d int
)
insert into #temp
values(1,0,1,1)
insert into #temp
values(1,0,1,2)
insert into #temp
values(2,0,1,1)`
insert into #temp
values(2,0,1,3);
This table is a result in a CTE , and I want to select the rows that have c and d unique, that means rows 1, 2 and 4 because in row 1 and 3 the pair c, d is 1, 1.
How can I do that inside a CTE(because I need the rows later in the CTE)? Or should I put the values into a temporary table and work with them?
If it was a simple table I could use a cursor for each combination, but this way it is not possible.
Can you help me?
10q
June 4, 2009 at 4:05 am
Can you be some more specific? there seems a lot may questions..
if you can provide your desired CTE code with sample output then it would be better...
"Don't limit your challenges, challenge your limits"
June 4, 2009 at 5:43 am
I only need the idea, how to make this happen.Because the query is pretty big and ugly, and I posted before some big scripts and I didn't receive an answer.
....I'll put it like this
So, having that table how can I extract the rows I said,having c and d unique.
One solution is a cursor....to extract each distinct pair from #temp and select top 1.
A better/easier solution?
June 4, 2009 at 5:51 am
Maybe I don't understand you correctly, but wouldn't this work?
SELECT * FROM myTable
where c d
[font="Verdana"]Markus Bohse[/font]
June 4, 2009 at 6:07 am
I want this to be returned
a b c d
1011
1012
2013
In first row c = d
I want the pair (c,d) to be selected only once.The other columns are not important.That means for pair (1,1) I can select a = 1 or a = 2. It doesn't matters.
June 5, 2009 at 2:41 am
I found the solution :
create table #temp
(
a int,
b int,
c int,
d int
)
insert into #temp
values(1,0,1,1)
insert into #temp
values(1,0,1,2)
insert into #temp
values(2,0,1,1)
insert into #temp
values(2,0,1,3);
--
select *
from #temp
where a in
(
select top 1 a
from #temp x
where x.c = #temp.c
and x.d = #temp.d
)
--
drop table #temp
I think this is the best one
But i have a question. I think is better to put a order by in the subquery, though it returns ok this way too.My question is : what criteria Sql Server uses when it returns the rows? In some cases the results are the same but in other they aren't.Could this be avoided without a order by clause?
10q
June 12, 2009 at 4:04 pm
You could create a clustered index on your #temp table. That would force the order to be however the index is ordered because at the very bottom of a clustered index is the actual table data.
create index idx_tmp on #temp (field [asc | desc], another, etc)
Randy
June 12, 2009 at 4:21 pm
Randy (6/12/2009)
You could create a clustered index on your #temp table. That would force the order to be however the index is ordered because at the very bottom of a clustered index is the actual table data.create index idx_tmp on #temp (field [asc | desc], another, etc)
There is no order in a table by definition. The only way to insure your results are in the correct order is to use an ORDER BY clause on the select statement.
Another approach would be to use ROW_NUMBER() in the query, as in:
;WITH myCTE AS (
SELECT a, b, c, d, ROW_NUMBER() OVER(PARTITION BY c, d ORDER BY a, b) AS rowNum
FROM dbo.MyTable)
SELECT * FROM myCTE WHERE rowNum = 1;
Lookup the ROW_NUMBER() function in BOL for further information. If you need additional help, review the first article I link to in my signature and re-post the question.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply