locked
How to add Identity column in a view RRS feed

  • Question

  • I would like to create a view with distinct values of Salary column. And I want to insert identity column ID for it.


    USE TEST_DW
    GO

    CREATE VIEW [Test3]
    AS

    SELECT  ROW_NUMBER() OVER (ORDER BY Salary)-1 AS ID,  DISTINCT Salary
       
    FROM [MyTable] WITH(NOLOCK)

    GO

    and I got incorrect syntax near 'DISTINCT'

     

    Thanks ,

    Alex

    • Edited by Plate11 Monday, December 12, 2011 3:30 PM
    Monday, December 12, 2011 3:28 PM

Answers

  • DISTINCT works on row level and you need to add DISTINCT at the beginning of SELECT (as shown below). Also, as you are using -1 for ROW_NUMBER(), the numbering would start from 0 instead of 1. 

    CREATE VIEW [Test3]
    AS
    
    WITH CTE AS
    (
    	SELECT	DISTINCT Salary
    	FROM	[MyTable]
    )
    SELECT	ROW_NUMBER() OVER (ORDER BY Salary) - 1 AS ID,  
    		Salary
    FROM	CTE
    
    GO

    Thanks!
    Monday, December 12, 2011 3:33 PM
  • Hi, try please :

    USE TEST_DW
    GO
    CREATE VIEW [Test3]
    AS
    SELECT ROW_NUMBER() OVER (ORDER BY Salary)-1 AS ID,Salary From
    (SELECT DISTINCT Salary FROM [MyTable] WITH(NOLOCK)) S
    

     

     

    Best regards
    • Marked as answer by Plate11 Monday, December 12, 2011 3:49 PM
    Monday, December 12, 2011 3:33 PM
  • Try 

     

    CREATE VIEW [Test3]
    AS
    with cte(Salary )as
    (
    select distinct Salary from  [MyTable] WITH(NOLOCK)
    )
    
    select ROW_NUMBER()over(order by Salary)-1 id, Salary from cte
    

    VT

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Edited by SimpleSQL Monday, December 12, 2011 3:38 PM
    • Marked as answer by Plate11 Monday, December 12, 2011 3:49 PM
    Monday, December 12, 2011 3:37 PM

All replies

  • DISTINCT works on row level and you need to add DISTINCT at the beginning of SELECT (as shown below). Also, as you are using -1 for ROW_NUMBER(), the numbering would start from 0 instead of 1. 

    CREATE VIEW [Test3]
    AS
    
    WITH CTE AS
    (
    	SELECT	DISTINCT Salary
    	FROM	[MyTable]
    )
    SELECT	ROW_NUMBER() OVER (ORDER BY Salary) - 1 AS ID,  
    		Salary
    FROM	CTE
    
    GO

    Thanks!
    Monday, December 12, 2011 3:33 PM
  • Hi, try please :

    USE TEST_DW
    GO
    CREATE VIEW [Test3]
    AS
    SELECT ROW_NUMBER() OVER (ORDER BY Salary)-1 AS ID,Salary From
    (SELECT DISTINCT Salary FROM [MyTable] WITH(NOLOCK)) S
    

     

     

    Best regards
    • Marked as answer by Plate11 Monday, December 12, 2011 3:49 PM
    Monday, December 12, 2011 3:33 PM
  • Try 

     

    CREATE VIEW [Test3]
    AS
    with cte(Salary )as
    (
    select distinct Salary from  [MyTable] WITH(NOLOCK)
    )
    
    select ROW_NUMBER()over(order by Salary)-1 id, Salary from cte
    

    VT

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Edited by SimpleSQL Monday, December 12, 2011 3:38 PM
    • Marked as answer by Plate11 Monday, December 12, 2011 3:49 PM
    Monday, December 12, 2011 3:37 PM
  • Thank you all,

    Your code works good.

    reordering the columns also works

    SELECT   DISTINCT Salary,
      ROW_NUMBER() OVER (ORDER BY Salary)-1 AS ID   
    FROM [MyTable] WITH(NOLOCK)

    Monday, December 12, 2011 3:49 PM
  • Thank you all,

    Your code works good.

    reordering the columns also works

    SELECT   DISTINCT Salary,
      ROW_NUMBER() OVER (ORDER BY Salary)-1 AS ID   
    FROM [MyTable] WITH(NOLOCK)


    Alex - The above one doesn't seem to eliminate duplicates as DISTINCT gets applied after calculating value for ID column. Verify once where the rows have same salary.

    Thanks!

    Monday, December 12, 2011 4:26 PM
  • Thanks for the post
    Tuesday, May 27, 2014 8:11 PM
  • >> I would like to create a VIEW with distinct values of “salary_something” column. And I want to insert IDENTITY column  [sic] id for it. <<

    “salary” by itself is meaningless; “_amt”? “_type”? 

    You do not know what a VIEW or IDENTITY are. VIEWs are virtual tables  that have no existence until they are invoked. An IDENTITY is a non-relational table property and not a column at all; it counts the physical insertion attempts to a base table. Your ROW_NUMBER() is not an IDENTITY. 

    SELECT DISTINCT is a row level operation; you got the syntax wrong! But ignoring that, since each ROW_NUMBER is unique, putting a SELECT DISTINCT in the query is silly; it does nothing. 

    Why do you need a ROW-NUMBER anyway? What does it mean in a valid data model? 

    CREATE VIEW Salary_Something_List
    AS
    SELECT DISTINCT salary_something
      FROM Foobar;

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

    Wednesday, May 28, 2014 2:08 AM