SQL Server Performance Forum – Threads Archive

Please help! How to search a table..

[?]
How do I write a query that searches through a table (containing information about persons) to find the oldest person registered and return all the information registered about this person?

Welcome to the fora here! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] It’s hard to tell without seeing your table structure. If you have some kind of registered date, something like this will work <pre id="code"> SELECT * FROM your_table WHERE registered_date = (SELECT MIN(registered_date) FROM your_table) </pre id="code"> or <pre id="code"> SELECT TOP 1 * FROM your_table ORDER BY registered_date </pre id="code"> If that doesn’t help you, please post your table structure. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Oops, looks like I’ve misinterpreted your question. It looks like you’re after the oldest person of age in your data. So, I guess, you really should provide your structure. Otherwise it’s like a shot in the dark. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

My table is like this:
Personid NCHAR(11)
Name NAVCHAR(20)
BirthDate DATETIME
Sex NCHAR(1)
..
..
. What I want to do is to search this table to find which person is the oldest and return all the information registered on this person (id, name, birth date and sex). Im a beginner at this so..Thanks
[:I]

Hi,
is this you want ? create table #tmptest
(
Personid NCHAR(11),
Name NVarCHAR(20),
BirthDate DATETIME,
Sex NCHAR(1)
)
go
insert into #tmptest
select ‘1’,’Abc1′,’19760102′,’m’ union all
select ‘2’,’Abc2′,’19770102′,’m’ union all
select ‘3’,’Abc3′,’19780102′,’m’ union all
select ‘4’,’Abc4′,’19790102′,’m’ union all
select ‘5’,’Abc5′,’19800102′,’m’ union all
select ‘6’,’Abc6′,’19810102′,’m’
go
select personid,name,datepart(yy,birthdate)as years,sex
from #tmptest
order by years
go
drop table #tmptest
go Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

ghemant, this wont work properly <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ordering by datepart ignores the month/days of birthdate, and so all people born in the same year will be in an undefined order. Id just use Franks suggestion of <pre id="code"> SELECT TOP 1 * FROM your_table ORDER BY registered_date </pre id="code">

Thank you all for your help.
I used the select top 1 * and it worked perfectly!

]]>