Need help with a simple query - 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)

Need help with a simple query

Author

Topic

Sincere
Starting Member

4 Posts

Posted - 2003-02-03 : 09:49:28

I need to build a query that can give me the following information from the table members.

- Name (Name)
- place of birth (POB)
- date of birth (DOB)

But I only want the oldest persons per place of birth. And it has to be possible that two persons have the same age in a certain place of birth. And it has to be done with sub-select(s).

Can some one help me ?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-02-03 : 12:50:34

SELECT Name FROM PeopleTable
WHERE DOB IN(
SELECT MAX(DOB) FROM PeopleTable
GROUP BY POB)

btw

quote:
And it has to be done with sub-select(s).

are you sure this is not homework?

OS

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-03 : 13:45:45

I Thought this was posted yesterday...there's a slight problem with the previous post by mohdowais. If a max data in one POB is not in the other it will bring it back too.

Check out the following:

Create Table #Temp_People (Name varchar(30), POB varchar(30), DOB datetime)
go

Delete From #Temp_People
Go

Insert Into #Temp_People (Name, POB, DOB) Select 'Ann', 'Washington', '1960-02-01'
Insert Into #Temp_People (Name, POB, DOB) Select 'Bob', 'Washington', '1960-02-02'
Insert Into #Temp_People (Name, POB, DOB) Select 'Frank', 'Washington', '1960-01-04'
Insert Into #Temp_People (Name, POB, DOB) Select 'Cloe', 'New York', '1960-01-03'
Insert Into #Temp_People (Name, POB, DOB) Select 'Dani', 'New York', '1960-01-04'
Insert Into #Temp_People (Name, POB, DOB) Select 'Ethel', 'New York', '1960-01-04'
go

Select * from #Temp_People
go

SELECT * FROM #Temp_People
WHERE DOB IN(
SELECT MAX(DOB) FROM #Temp_People
GROUP BY POB)
go

Select * from #Temp_People o
inner join (Select POB, Max(DOB) As Max_DOB From #Temp_People i Group By POB) As XXX
On xxx.POB = o.POB And xxx.Max_DOB = o.DOB

Drop Table #Temp_People

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-02-04 : 01:45:28

Ahhh! thanks Brett, there is always something that you miss when you are wondering about menial things such as "is this a homework assignment?" or "what is the meaning of life?"

Sincere
Starting Member

4 Posts

Posted - 2003-02-04 : 09:00:08

No this is not a homework assignment. Because I graduated 3 years ago.

I mentioned the sub-select thing, because my boss asked if it could be done that way. My answer was yes. He thought it couldn't be done that way.

I couldn't solve this query on my own ( my boss neither ), because we're both not experts in SQL. You can call us beginners even. This is because we started experimenting with SQL 2 weeks ago. For a new project that requires SQL.

So there's your answer when you start to worry about those menial things again !!

Thanks for the help X002548 I will let you know if it worked.

Sincere.

P.S. Sorry I posted it twice I was inpatient

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-04 : 09:04:02

My Pleasure, and if you need any more assistance, please let me know

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources