how to retrive data using row id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to retrive data using row id

Author

Topic

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 05:35:48

Hi All,

as im working in SQL2000, i want to know how to retrive data using row_id.

Regrads & Thanks

Damodar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-24 : 05:40:55

select * from yourTable
where row_id = someId

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 05:44:19

Hi spirit1,

i came to know that in oracle and sql 2005 we have the rowid for a particular row in a table, bt in sql 2000 it was there.

Damodar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-24 : 05:53:15

no there isn't one in sql server 2000.
explain what you're trying to achieve so we can help you easier.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 06:18:56

hi Spirit 1,

aplcnt_id seq_num from_year to_year is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2005 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null
1 7 1988 2000 null

in the above example there is no gap of years. u may ask that when compared 2 and 3 record there is the gap between 2005 to 2006. bt applcnt may think of this way jan 2006 to sone 2007 and 2004 to december 2005 there fore there is no gap.

and one more thing is that max(from_year)-min(from_year)>=7

for the above i got the answer from pbguy as below

declare @t table (applicnt_id int, seq_num int, from_year int,to_Year int, is_primary_ind varchar(1))
Insert @t
select 1,1,2007,Null,'Y' union
select 1,2,2006,2007,Null union
select 1,3,2004,2005,Null union
select 1,4,2002,2004,Null union
select 1,5,2002,2002,Null union
select 1,6,2000,2001,Null union
select 2,1,1998, 2000, NULL

Select c.applicnt_id , 'Year Missing' = cast(c.to_year as varchar(4)) + '-' + cast(d.from_year as varchar(4))
from @t c join
(Select a.applicnt_id, seq_num, from_year, to_Year, is_primary_ind
from @t a left outer join
(Select applicnt_id from @t group by applicnt_id
having Max(to_year) - Min(from_year) >= 7) as b on a.applicnt_id = b.applicnt_id) as d
on c.applicnt_id = d.applicnt_id and c.seq_num = d.seq_num +1 and abs(c.to_year - d.from_year) > 1

bt in this he used seq_num to compare the years, if we delete any particualr record then seq_num will be changed and above auery will fails. i thought to use rowid of a particular row in that table. is it possible???

as if pdguy says that he was leaving im asking u. plzz asmwer me as soon as possible.

Regrads & thanks

Damodar

cardgunner

326 Posts

Posted - 2007-05-24 : 08:29:51

Damodar,

I don't have the answer however maybe I understand the question.

From aplcnt_id seq_num from_year to_year is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2005 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null
1 7 1988 2000 null

you want a result as "Skipped Years
Applicant_ID, Year Skipped
1 2005 to 2006
1 2001 to 2002

Is this what you are looking for?

Card Gunner

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 08:35:53

Hi cardgunner,

Ya Exactly, bt from above exaple there is no year gap that was also explained in the post. if at all year gap is >1 then we have to consider there is year gap.

Regards

Damodar

cardgunner

326 Posts

Posted - 2007-05-24 : 08:44:42

You are right, it was posted that there was no gap. however we are asking to "ASSume" the months.
So if the the below table was correct:

aplcnt_id seq_num from_year to_year is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2004 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null
1 7 1988 2000 null

then the result would be

Applicant_id, Skip Years
1 2004 to 2006

This is because the difference from to_year and from year in row 2-3 is >1?

Card Gunner

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 08:49:18

Hi cardgunner,

ya absolutly rite, bt we should nt consider there months.

if at all year gap >1 then there is gap other wise no gap

cardgunner

326 Posts

Posted - 2007-05-24 : 08:58:22

Well Like I said I cannot provide an answer. I'm 2 months into SQL learning the hard way. But I hope that this may bring light to what you are looking for so the pros can comment.

Card Gunner

damuchinni
Starting Member

29 Posts

Posted - 2007-05-28 : 00:54:45

Hi all,

How can i get a roe id in sql 2000

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 01:09:00

This should get you started with what you want (without using ROWID):

-- prepare sample data
declare @t table
(
	a int
)

insert @t
select 2007 union all
select 2006 union all
select 2004 union all
select 2003 union all
select 2001

-- actual output
select t1.a as [From], t1.a+1 as [To]  from @t t1
where t1.a+1<(select min(a) from @t t2 where t2.a>t1.a)
order by t1.a

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources