Min. of datetime in Sybase.

David Jude

unread,

Dec 7, 1998, 3:00:00 AM12/7/98

to

System - Sybase 10.x - PB 5.04

I have a column of type, DateTime. I am trying to query for the most current
row using

Select Min( Tran_date) from ...

The system seems to be hanging.

Could anyone please let me know, if I can use the Min function on a datetime
field, or if there is any other way to get the min value from a datetime column.

Thanks for helping out.

David

akak...@aol.com

Steve Katz [TeamPS]

unread,

Dec 8, 1998, 3:00:00 AM12/8/98

to David Jude

I just tried this with no problems on a System 11 database within PB.
From a T-SQL perspective, min() is valid on any datatype.

Have you tried executing the query from the Database Administration
Notepad within PB?

steve
[TeamPS]

David Jude

unread,

Dec 9, 1998, 3:00:00 AM12/9/98

to

On Tue, 08 Dec 1998 10:41:27 -0500,
in powersoft.public.powerbuilder.database

Thank You Steve. I tried again, with your reassurance, this time with a lesser
number of records, and it works fine.

I was testing it out from the Database Administrator.

I tried, 'Explain Sql', and it seems that in the case of
Select Min (Tran_Date), the query is using the index on Tran_Date and not the
index on the columns of the where clause, resulting in, I presume a Table Scan.
Without the Min(), doing just a Select * it is using the index on the columns of
the where clause. Presently, I get an acceptable performance, by populating a
temp table and querying it.

Is there a way, I can force usage of an index? I would like to do it in a Stored
Procedure. My system is Sybase.

Thank You
David

Rajendra Mandalia

unread,

Dec 9, 1998, 3:00:00 AM12/9/98

to

David,

This works Sybase X.x / Sybase XI.x / MS SQL 6.5. Maybe an index would help in case
your table is very very large and this is a frequent query. You sure the system
hangs ?

Cheers,
Rajendra Mandalia
MCP.

David Jude wrote:

>
>
> System - Sybase 10.x - PB 5.04
>
> I have a column of type, DateTime. I am trying to query for the most current
> row using
>
> Select Min( Tran_date) from ...
>
>
> The system seems to be hanging.
>
> Could anyone please let me know, if I can use the Min function on a datetime
> field, or if there is any other way to get the min value from a datetime column.
>
> Thanks for helping out.
>
> David
>
>

> akak...@aol.com

Steve Katz [TeamPS]

unread,

Dec 10, 1998, 3:00:00 AM12/10/98

to David Jude

This is somewhat strange since the use of an aggregate in the result set
should not affect the use of the index to execute the where clause. The
where clause will generate a temp result set upon which the aggregate
will be executed to generate the final result set.

I just tried this with a minimal table and the Explain SQL was no
different if I used min(dt_col) or just dt_col.

I don't purport to be an expert in System 10, so I could be wrong.

steve
[TeamPS]

David Jude wrote:
>
> On Tue, 08 Dec 1998 10:41:27 -0500,
> in powersoft.public.powerbuilder.database
> Steve Katz [TeamPS] <kat...@jpmorgan.com> wrote:
> >I just tried this with no problems on a System 11 database within PB.
> >From a T-SQL perspective, min() is valid on any datatype.
> >
> >Have you tried executing the query from the Database Administration
> >Notepad within PB?
> >
> >steve
> >[TeamPS]
> >

> >David Jude wrote:
> >>
> >>
> >>
> >> System - Sybase 10.x - PB 5.04
> >>
> >> I have a column of type, DateTime. I am trying to query for the most current
> >> row using
> >>
> >> Select Min( Tran_date) from ...
> >>
> >>
> >> The system seems to be hanging.
> >>
> >> Could anyone please let me know, if I can use the Min function on a datetime
> >> field, or if there is any other way to get the min value from a datetime
> column.
> >>
> >> Thanks for helping out.
> >>
> >> David
> >>
> >>
> >>
>