You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Ashish Kulkarni <ku...@yahoo.com> on 2006/02/24 16:58:16 UTC
[OT] Need help with SQL Query
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
Re: [OT] Need help with SQL Query
Posted by Ashish Kulkarni <ku...@yahoo.com>.
Hi
I used the following sql statement to get it working
select date, time, name from mytable
number=123 order by date, time
FETCH FIRST 1 ROWS ONLY
--- Nathan Maves <Na...@Sun.COM> wrote:
> A little hasty with my answer...
>
> The query I gave could give you results from two
> different rows.
>
> Having a date and time column is problematic. I
> would just use a
> date column that included the time or a timestamp.
>
> If there is only one column to worry about then you
> could do
>
> select date, name from mytable where number = 123
> and date = (select
> min(date) from mytable where number = 123)
>
> nathan
>
>
> On Feb 24, 2006, at 9:25 AM, Nathan Maves wrote:
>
> > You almost had it :)
> >
> > select min(date), min(time), name from mytable
> where number = 123
> > group by name
> >
> >
> > Nathan
> >
> > On Feb 24, 2006, at 8:58 AM, Ashish Kulkarni
> 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
> >
>
>
A$HI$H
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: [OT] Need help with SQL Query
Posted by Nathan Maves <Na...@Sun.COM>.
A little hasty with my answer...
The query I gave could give you results from two different rows.
Having a date and time column is problematic. I would just use a
date column that included the time or a timestamp.
If there is only one column to worry about then you could do
select date, name from mytable where number = 123 and date = (select
min(date) from mytable where number = 123)
nathan
On Feb 24, 2006, at 9:25 AM, Nathan Maves wrote:
> You almost had it :)
>
> select min(date), min(time), name from mytable where number = 123
> group by name
>
>
> Nathan
>
> On Feb 24, 2006, at 8:58 AM, Ashish Kulkarni 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
>
Re: [OT] Need help with SQL Query
Posted by Nathan Maves <Na...@Sun.COM>.
You almost had it :)
select min(date), min(time), name from mytable where number = 123
group by name
Nathan
On Feb 24, 2006, at 8:58 AM, Ashish Kulkarni 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
Re: [OT] Need help with SQL Query
Posted by Vincent NICOLAS <vi...@teamlog.com>.
Maybe the "having" clause :
select date, time, name from mytable
having date = min(date)
Ashish Kulkarni a écrit :
> 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
>
>
--
Vincent NICOLAS
Teamlog, Lyon
Re: [OT] Need help with SQL Query
Posted by Jeff Butler <je...@gmail.com>.
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 <ku...@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
>