Results 1 to 15 of 15

Thread: Max(RowId)

  1. #1
    Join Date
    Oct 2004
    Posts
    8

    Unanswered: Max(RowId)

    do we have max(rowid) kind of stuff in sql server as we have in oracle?
    TIA

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If you are referring to rowid as a column name in the table, yes. If you are referring to rowid as an internal pointer withing the table, no. The structure for the function is the same ... select max(<column_name>) from <table_name>

  3. #3
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    Quote Originally Posted by tomh53
    If you are referring to rowid as a column name in the table, yes. If you are referring to rowid as an internal pointer withing the table, no. The structure for the function is the same ... select max(<column_name>) from <table_name>
    Hi tomh53,

    How can I get rowid in SQL Server tables...

    I tried,

    SELECT rowid
    FROM tableName

    but it doesn't work...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jay82
    How can I get rowid in SQL Server tables...
    you cannot, there is no such concept

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by r937
    you cannot, there is no such concept

    Rudy ... don't you just love it when they don't take the time to read the full answer ...RTFA !!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    John and Jay,

    I am sure there is a way to do what you want to do in SQL Server, though it may require a different approach than in Oracle.

    Explain your requirements (why do you need RowID?) and someone on the forum can help you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2003
    Posts
    357
    Here is a way to generate rowid

    Select RowId=(Select count(*) from table where Field<=T.Field),* from Table T
    Last edited by Madhivanan; 02-24-05 at 01:51.
    Madhivanan

    Failing to plan is Planning to fail

  8. #8
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    I think, I agree with r937...

    I wanted it for table where there is no primary key... and wanted to select perticular record, for example,

    FirstName LastName
    --------- ---------
    abc xyz
    def jop
    def jop

    Now If I want to delete second row, how do i do it in SQL Server 2000?? I couldn;t find anyway to do that... I know it's really bad database design.. but what if you want to do it.

  9. #9
    Join Date
    Oct 2003
    Posts
    357
    Hi jay82

    Try this

    Code:
    alter table TableName ADD id int identity
    go
    select * from TableName
    go
    Delete from TableName  where id not in (select max(id) from TableName group by FirstName,LastName)
    go
    alter table TableName drop column id 
    go
    select * from TableName
    Madhivanan

    Failing to plan is Planning to fail

  10. #10
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    Hi Madhivanan,

    I am sure that will work.. In oracle we have concept of rowid which is really helpful..

    Thanks

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The idea of rowid was necessary in Oracle to work around some of Oracle's other limitations. Rowid actually warps the thinking of most Oracle users so that they unconsciously code around those limitations, and come to think of rowid as a feature instead of a work-around.

    The relational algebra gurus go ballistic when the concept of rowid gets mentioned, regardless of what you call it. The whole concept is flawed from a ra standpoint, because it artificially introduces an order that just isn't there in the underlying data.

    I tend to disdain rowid because it truly does warp the mindset. It becomes a crutch that is so comfortable that the average user will never willingly let it go, even once they see the benefits of doing without it.

    -PatP

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the table you are dealing with is large, you may want to go a bit more of a roundabout (but probably more efficient) way.

    Select the duplicate rows into a temp table

    Delete the duplicates from the original table (yes, this leaves you with no records valid or duplicate)

    Select distinct rows back into the original table from your temp table.

    Just be careful with those deletes, and keep those backups handy.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He hasn't said anything about wanting to delete duplicates...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sorry. I confused post #8 with the original poster.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The physical order of data in a database has no meaning...even in Oracle..

    Such that if you "hold on" to a rowid to use it to identify a row, and there is data that's inserted, updated and deleted, and then the data is reorganized, that rowid may well point to a different row

    You're much better off creating a natural key, or (oh good lord) an IDENTITY Column that will always be assoicated with that row.

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •