subquery select - SQL Server Forums

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)

subquery select

Author

Topic

mli
Starting Member

3 Posts

Posted - 2007-01-31 : 09:49:42

RowID SSN dateofhire dateoftermination Facility
1 8888 12/28/2000 3/2/2006 XX
2 8888 12/28/2000 xx
3 9999 3/5/2001 xx
4 9999 3/5/2001 3/4/2006 xx
5 7777 6/7/2002 12/12/2006 xx
6 7777 6/7/2002 3/4/2006 xx

I want select RowID is 2, 3, 5

how can I write select statement? Thanks very much!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 09:53:50

[code]
select *
from table
where RowID in (2, 3, 5)
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-31 : 09:56:57

RowID is the column in your table or row number indicator?

If it's the later one, you will need to tell us the criteria for assigning the Row IDs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mli
Starting Member

3 Posts

Posted - 2007-01-31 : 10:04:24

sorry I did not clarify the question. company has several facility.employee may be terminated by one facility, then work for another facility. dateof hire are always same for one employee. If they still work in the company, date of termination will be null. I want select the last facility he work. RowID is identity field. it can not be used for dateoftermination sequence. xx is facility name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 10:11:24

[code]
declare @table table
(
RowID int,
SSN int,
dateofhire datetime,
dateoftermination datetime,
Facility varchar(10)
)

insert into @table
select 1, 8888, '12/28/2000', '3/2/2006', 'XX' union all
select 2, 8888, '12/28/2000', NULL, 'xx' union all
select 3, 9999, '3/5/2001', NULL, 'xx' union all
select 4, 9999, '3/5/2001', '3/4/2006', 'xx' union all
select 5, 7777, '6/7/2002', '12/12/2006', 'xx' union all
select 6, 7777, '6/7/2002', '3/4/2006', 'xx'

select *
from @table t
where RowID = (select top 1 RowID from @table x where x.SSN = t.SSN order by isnull(dateoftermination, '99991231') desc)

RowID SSN dateofhire dateoftermination Facility
----------- ----------- ----------- ------------------ ----------
2 8888 2000-12-28 NULL xx
3 9999 2001-03-05 NULL xx
5 7777 2002-06-07 2006-12-12 xx

[/code]

mli
Starting Member

3 Posts

Posted - 2007-01-31 : 10:42:02

khtan,
You are Terrrriiifficc…….
Thanks so much!!!

mli

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources