Max(RowId)

do we have max(rowid) kind of stuff in sql server as we have in oracle?
TIAIf 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>|||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...|||How can I get rowid in SQL Server tables...you cannot, there is no such concept

:)|||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 !!|||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.|||Here is a way to generate rowid

Select RowId=(Select count(*) from table where Field<=T.Field),* from Table T|||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. :)|||Hi jay82

Try this

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|||Hi Madhivanan,

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

Thanks :)|||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|||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.|||He hasn't said anything about wanting to delete duplicates...|||Sorry. I confused post #8 with the original poster.|||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