SQL Server Performance

problem with row_number()

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Nov 7, 2009.

  1. shankbond New Member

    Hi,
    I have a stored procedure which is getting two arguments
    Now I have basically a Union clause between two different joined tables ,since it is a union I cannot have a unique identity field Which I need and should only be an integer value.
    Row_number() can give me such thing But I don't know where should I apply it?
    1) I cannot apply it in both parts of the union
    2)Is it possible that:
    SELECT ROW_NUMBER() OVER(ORDER BY t.name) , the fields in the union table FROM procedure;
    Basically I want to add a rownumber to the result produced through the procedure
    Sorry for my bad english if any:(
    Any help shall be appreciated.
  2. Madhivanan Moderator

    Post the code used in the procedure
  3. KarenWallace New Member

    Can you put the row_number() on the results of the union?


    SELECT t.name, ROW_NUMBER() OVER (ORDER BY t.name)FROM ( SELECT name FROM a UNION SELECT name FROM b) t
  4. shankbond New Member

    I found the solution later in the night
    Thanks any way guys
    To Karen,
    [quote user="KarenWallace"]Can you put the row_number() on the results of the union?[/quote]
    Yes for this problem I found a solution later in the night.
    It should have been
    SELECT *,ROW_NUMBER() OVER (OREDER BY name) AS name FROM
    ( SELECT name FROM a
    UNION
    SELECT name FROM b )t
  5. kopaks New Member

    i have error in this code below:
    "
    SELECT *,ROW_NUMBER() OVER (OREDER BY name) AS name FROM
    ( SELECT name FROM a
    UNION
    SELECT name FROM b )t where name between (1) and (10) "...do u can help me??thanks b4...
  6. Adriaan New Member

    I see a typing error here:
    OREDER BY
    That gives you two errors:Incorrect syntax near 'OREDER'.
    Incorrect syntax near 't'.

Share This Page