🚀 go-pugleaf

On 19 Dec, 07:35, "Martijn Tonies" <m.ton...@upscene.removethis.com>
wrote:
> > But I thought that would give me multiple rows for the same day. Have run
> it
> > and this happens indeed.
> > Maybe I am missing something here.
>
> Are the F2 values different?
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
> MS SQL Server
> Upscene Productionshttp://www.upscene.com
> My thoughts:http://blog.upscene.com/martijn/
> Database development questions? Check the forum!http://www.databasedevelopmentforum.com
>
>
>
> > RBS
>
> > "Martijn Tonies" <m.ton...@upscene.removethis.com> wrote in message
> >news:47683fec$1@newsgroups.borland.com...
> > >> Trying to solve the following problem.
> > >> I have quite a large table with 2 fields, a unique field with integer
> > >> number, call this F1 and
> > >> a field with datetime values, call this F2.
> > >> Both fields will appear in ascending order when you do a select.
> > >> F1 is indexed, but F2 is not.
>
> > >> To show a small sample:
>
> > >> F1                    F2
> > >> --------------------------------------
> > >> 10303521     01/04/2006 19:50
> > >> 10303525     01/04/2006 19:50
> > >> 10303529     01/04/2006 19:50
> > >> 10303535     01/04/2006 19:51
> > >> 10303536     02/04/2006 07:43   <<<
> > >> 10303540     02/04/2006 07:43
> > >> 10303541     02/04/2006 07:43
> > >> 10303546     02/04/2006 07:43
>
> > >> Now what I need to do is fetch all the rows that are the first row for
> > >> the
> > >> particular day, so
> > >> in the above example I need the row indicated with <<<
>
> > >> Now maybe I could do this with a GROUP BY and CAST(F2 AS INTEGER) but I
> > >> can't work it out.
>
> > > Why the CAST? Why not simply the GROUP BY on F2?
>
> > > SELECT MIN(F1), F2
> > > FROM ...
> > > GROUP BY F2
>
> > > Given that the date and time is the same in F2
>
> > >> Any suggestions how to tackle this? As this is a large table (over 20
> > >> million rows) and as F2 is not
> > >> indexed maybe this will too slow in SQL and maybe I need an algorithm
> for
> > >> this in my application.
>
> > >> RBS
>
> > > --
> > > Martijn Tonies
> > > Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB,
> Oracle
> > > &
> > > MS SQL Server
> > > Upscene Productions
> > >http://www.upscene.com
> > > My thoughts:
> > >http://blog.upscene.com/martijn/
> > > Database development questions? Check the forum!
> > >http://www.databasedevelopmentforum.com

Yes, they are, as in the posted sample.
I have solved this now by a binary chop and search coded in my app.
This is very fast and as F2 is not indexed I don't think I can improve
on that with a pure SQL solution.

RBS