none
Is there anything like rowid, rownum like in MySql and Oracle??

    Question

  • Hi,

    i am new to SQL Server. I want to write a query where in i want to delete duplicate rows from a table keeping the master copy.

    If it is MySQL or Oracle we can write that using built in rownum or rowid. How to do that task in SQL Server 2005. Is there anything like rowid, rownum in SQL Server?? If not suggest me a way to do that??

    ...aazad

    Thursday, November 23, 2006 2:41 PM

All replies

  •   not exactally , but u can make use of 'TOP' or row_number() function..

    select top 1 from table 1 order by column1

    u can use top intelligently to get top/bottom  nth row... given u have somethin to orderby

     

    Thursday, November 23, 2006 3:10 PM
  • Rownum is a psuedo column that generates a logical sequence number so it will change depending on the query execution plan, data etc. Rowid on the other hand is a physical identifier (at least in Oracle). So how are you using these in your queries? What is the purpose of using something like ROWID? You do have primary key or unique key constraints on your tables right! It will be easier to suggest the alternatives if we know your use cases.
    Thursday, November 23, 2006 11:06 PM
  • Hi chandar,

    There can be a senario where a table has no primary key and has data. Later when i want to make a column as primary key, i need to delete the duplicates, which i dont want to do it manually. so i shud write a query where i can delete duplicate rows keeping one copy of it. I worked with MySQL and in MySQL i can write a query as follows

    delete from test where rowid in ( select rownum from test where rownum not in ( select min(rownum) from test group by all_columns having count(*) > 1 ) group by all_columns having count(*) > 1 

    The above code deletes the duplicates the master copy in MySQL. I am using that logical column rownum. How to do the same job in SQL Server 2005??

    Regards..,

    Aazad

     

     

     

     

     

    Friday, November 24, 2006 9:13 AM
  •   okk...lect us say u want to make column1 as ur primary key in table1 , so to find out the duplicate(or more) entries of this key , use the following query...

    select column1 from table1

    group by column1

    having count(column1)>1

    this will enlist all the entries for column1 which r repeating...

     

    Friday, November 24, 2006 9:27 AM
  • Thank god .. you are using SQL Server 2005 use the following query

    Example:

    CREATE TABLE Table1

    (

    [Id] [int] NULL

    )

    go

    INSERT INTO Table1 values(10);

    INSERT INTO Table1 values(10);

    INSERT INTO Table1 values(20);

    INSERT INTO Table1 values(20);

    go

     

    With Test(rownum,ID)

    as

    (

    Select Row_Number() OVER (ORDER BY ID), * From Table1

    )

    Delete From Test Where rownum in

    (

    Select A.rownum From Test A JOIN Test B On B.Id=A.ID and A.rownum >= B.rownum

    Group bY A.rownum,A.ID Having Count(A.ID) <> 1

    )

    Friday, November 24, 2006 9:29 AM
  • hi
    use newid() function
    good luck
    Friday, November 24, 2006 8:10 PM