Help needed with a simple querie

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)

Help needed with a simple querie

Author

Topic

Sincere
Starting Member

4 Posts

Posted - 2003-02-03 : 09:48:39

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 ?

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-03 : 10:00:50

SELECT l.Name, l.POB, l.DOB
FROM table l INNER JOIN
(SELECT POB, MAX(DOB) AS Max_DOB FROM Table GROUP BY POB) AS r
ON l.POB = r.POB AND l.DOB = r.Max_DOB

Hope this works

Brett

8-)

Sincere
Starting Member

4 Posts

Posted - 2003-02-04 : 14:54:19

Okay as promised, Brett.

The SQL code didn't work I get the following general warning:

" Null value eliminated in aggregate function "

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-04 : 14:59:24

That's just a warning message. It's just telling you that eliminated rows from the scalar function. There are a couple of ways to eliminate it by using setting (set ansi_nulls on for example), but I personally don't reccomend it.

You can fix it with SQL Like:

SELECT l.Name, l.POB, l.DOB
FROM table l INNER JOIN
(SELECT POB, MAX(IsNull(DOB,1900-01-01)) AS Max_DOB FROM Table GROUP BY POB) AS r
ON l.POB = r.POB AND l.DOB = r.Max_DOB

As Long as the bogus Date value doesn't impact your results.

Hope this helps

Brett

8-)

PS. Just for kicks though, go to BOL and Look up ANSI_NULLS to get a better understanding of what's going on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources