none
One table, 2 columns same name. Problem? RRS feed

  • Question

  • SQL Sever 2008R2

    I'm surprised that t-sql did not complain about two columns having the same name ('Seq') in the ranking query below

    DECLARE @Table1 TABLE
    ( CustomerID char(6), ProductID char(4), OrderDate date, Seq tinyint, aValue float, FieldA char(4), FieldB char(4) );
    INSERT INTO @Table1
     VALUES('AAAA', '101', '20120701', 0, 9.99, 'blah', 'blah')
      ,('BBBB', '101', '20120701', 0, 9.99, 'blah', 'blah')
      ,('CCCC', '101', '20120701', 0, 9.99, 'blah', 'blah')
      ,('AAAA', '101', '20120701', 0, 9.99, 'blah', 'blah')  


    SELECT * FROM @Table1

    SELECT *,
     ROW_NUMBER() OVER (PARTITION BY CustomerID, ProductID, OrderDate ORDER BY CustomerID, ProductID, OrderDate) AS Seq
    FROM @Table1


    Karl Thompson

    Sunday, July 15, 2012 4:37 PM

Answers

  • You are missing a lot of basic concepts and a forum is not the place to learn the. Look up the term "closure" in a math book. It means that an operation on elements of a set produce a result in that set. If I add integers, I get an integer. 

    The result of a query, which is an operation on tables, is a NEW table. This is not the original tables at all. So you can name the columns anything you want. by default, the result inherits the non-ambiguous names from the inputs. 

    In fact when you do a UNION, EXCEPT or INTERSECT the result has no column names. Likewise, a derived table expression needs names for itself and its columns. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by Karl T_ Sunday, July 15, 2012 6:12 PM
    Sunday, July 15, 2012 5:17 PM
  • Hello Karl,

    In "one table" you can't have 2 columns with the same name; but this is "one query" and here you can have columns with equal names; why not? In common you address a column by it's ordinal.

    SELECT
       'Col1' AS A
      ,'Col2' AS A
      ,'Col3' AS A


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    • Edited by Olaf HelperMVP Sunday, July 15, 2012 5:26 PM
    • Marked as answer by Karl T_ Sunday, July 15, 2012 6:12 PM
    Sunday, July 15, 2012 5:25 PM

All replies

  • You are missing a lot of basic concepts and a forum is not the place to learn the. Look up the term "closure" in a math book. It means that an operation on elements of a set produce a result in that set. If I add integers, I get an integer. 

    The result of a query, which is an operation on tables, is a NEW table. This is not the original tables at all. So you can name the columns anything you want. by default, the result inherits the non-ambiguous names from the inputs. 

    In fact when you do a UNION, EXCEPT or INTERSECT the result has no column names. Likewise, a derived table expression needs names for itself and its columns. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by Karl T_ Sunday, July 15, 2012 6:12 PM
    Sunday, July 15, 2012 5:17 PM
  • Hello Karl,

    In "one table" you can't have 2 columns with the same name; but this is "one query" and here you can have columns with equal names; why not? In common you address a column by it's ordinal.

    SELECT
       'Col1' AS A
      ,'Col2' AS A
      ,'Col3' AS A


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    • Edited by Olaf HelperMVP Sunday, July 15, 2012 5:26 PM
    • Marked as answer by Karl T_ Sunday, July 15, 2012 6:12 PM
    Sunday, July 15, 2012 5:25 PM