🚀 go-pugleaf

> 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