problem with query in CTE

  • 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

  • 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"

  • 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?

  • Maybe I don't understand you correctly, but wouldn't this work?

    SELECT * FROM myTable

    where c d

    [font="Verdana"]Markus Bohse[/font]

  • 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.

  • 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

  • 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

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    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