ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: [OT] Need help with SQL Query
Date Fri, 24 Feb 2006 16:25:25 GMT
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 <kulkarni_ash1312@yahoo.com> 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
>

Mime
View raw message