IMHO that's a pretty poor table design - min(date) and min(time) may not give you the exact record you want - because they are treated as unrelated. It would be better to use a timestamp field, then do something like this:
<sql>
select name
from myTable
where number=123
and timestamp = (select min(timestamp) from mytable where number=123)
</sql>
Here's one way to do the query you described, but again this might not really give you the record you want:
<sql>
with temp (date, time) as (select min(date), min(time) from myTable where number=123)
select name
from mytable join temp on name.date = temp.date and
name.time = temp.time and number = 123
</sql>
Jeff Butler
On 2/24/06, Ashish Kulkarni <[EMAIL PROTECTED]> wrote:
Hi
I have a table with 4 columns number, name, date,
time, it is not unique so can have multiple entries
for one name at different date and time.
I need to find out the first instance of name for that
number in this table, or get the name with minimum
date and time
i can do
select min(date), min(time) from mytable where
number=123
this will give me the min date and time , but how can
i get the name also,
for example
select min(date), min(time), name from mytable
number=123
wont work
Ashish
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
