Post reply

CTE DML

  • June 17, 2014 at 6:21 am

    #1722286

    SqlMel (6/17/2014)

    I actually thought "select distinct * ..." might raise an error.

    I have never used this and wonder why it would even be allowed.

    A "SELECT DISTINCT * ..." is just to return a resultset where each (complete) row is unique. It's the same as a GROUP BY on all columns. The DISTINCT has the same performance costs as a GROUP BY on all selected columns.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • June 17, 2014 at 6:29 am

    #1722288

    BWFC (6/17/2014)

    rhythmk (6/17/2014)

    BWFC (6/17/2014)

    rhythmk (6/17/2014)

    Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

    Could you enlighten me on that please? It sounds very useful.

    Here you go.

    CREATE TABLE #TEMP (ID INT,FNAME VARCHAR(50),LNAME VARCHAR(50))

    GO

    INSERT INTO #TEMP

    SELECT 1,'LIONEL','MESSI' UNION ALL

    SELECT 1,'LIONEL','MESSI' UNION ALL

    SELECT 1,'LIONEL','MESSI' UNION ALL

    SELECT 2,'NEYMAR','SILVA' UNION ALL

    SELECT 2,'NEYMAR','SILVA' UNION ALL

    SELECT 3,'CHRISTIANO','RONALDO' UNION ALL

    SELECT 3,'CHRISTIANO','RONALDO' UNION ALL

    SELECT 4, 'THOMAS','MUELLER'

    GO

    --SELECT * FROM #TEMP

    ;WITH DUPL AS

    (SELECT ID,FNAME,LNAME,ROW_NUMBER() OVER (PARTITION BY ID,FNAME,LNAME ORDER BY ID,FNAME,LNAME) [RN]

    FROM #TEMP)

    DELETE FROM DUPL WHERE RN <> 1

    --SELECT * FROM #TEMP

    --DROP TABLE #TEMP

    I like that, there's another one to add to the toolbox. Thanks.

    Interesting 🙂

  • June 17, 2014 at 6:46 am

    #1722292

    Nice QoD

  • June 17, 2014 at 7:08 am

    #1722299

    I had set myself a task to increase my knowledge of CTEs and found some interesting things. I didn't know you could use a CTE to modify existing data either.

    The DISTINCT was a red herring for those who noticed I had the same student in there twice... 🙂

    There are no facts, only interpretations.
    Friedrich Nietzsche

  • June 17, 2014 at 7:17 am

    #1722303

    You learn something new every day...The multiple rows on an Insert Into/Values fails on older versions of SQL, but has been added to SQL 2008+

  • June 17, 2014 at 7:24 am

    #1722307

    BWFC (6/17/2014)

    rhythmk (6/17/2014)

    Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

    Could you enlighten me on that please? It sounds very useful.

    Yes, please.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • June 17, 2014 at 7:33 am

    #1722313

    domenico.delbrocco (6/17/2014)

    I've never thought about insert data in a CTE

    Same here 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • June 17, 2014 at 8:07 am

    #1722332

    nice question.. some what useful for interview preparation....

    Manik
    You cannot get to the top by sitting on your bottom.

  • June 17, 2014 at 11:02 am

    #1722419

    BWFC (6/17/2014)

    rhythmk (6/17/2014)

    Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

    Could you enlighten me on that please? It sounds very useful.

    Here is an article on it

    http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 17, 2014 at 11:04 am

    #1722422

    Straight forward.

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 17, 2014 at 11:38 am

    #1722430

    I still don't use CTEs often but that script to delete duplicates has me reconsidering their importance! Very cool. Nice question. Thanks Barry.

  • June 17, 2014 at 12:57 pm

    #1722459

    SQLRNNR (6/17/2014)

    BWFC (6/17/2014)

    rhythmk (6/17/2014)

    Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

    Could you enlighten me on that please? It sounds very useful.

    Here is an article on it

    http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

    Thanks. Very nice.

  • June 17, 2014 at 1:16 pm

    #1722480

    marcia.j.wilson (6/17/2014)

    SQLRNNR (6/17/2014)

    BWFC (6/17/2014)

    rhythmk (6/17/2014)

    Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

    Could you enlighten me on that please? It sounds very useful.

    Here is an article on it

    http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

    Thanks. Very nice.

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 17, 2014 at 9:46 pm

    #1722568

    Thank you very much for the question.

    Please enlighten:-

    Should we select from the CTE expression name instead of the actual table - Student_CTE vs #Students?

  • June 17, 2014 at 10:45 pm

    #1722578

    Easy, but a nice learning,,,!!!

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply