|
| group by cast datetime as integer? |
 |
Tue, 18 Dec 2007 20:39:04 -000 |
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.
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
|
| Post Reply
|
| Re: group by cast datetime as integer? |
 |
Tue, 18 Dec 2007 22:36:14 -000 |
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.
RBS
"Martijn Tonies" <m.tonies@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
>
>
|
| Post Reply
|
| Re: group by cast datetime as integer? |
 |
Tue, 18 Dec 2007 22:48:11 +010 |
> 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
|
| Post Reply
|
| Re: group by cast datetime as integer? |
 |
Wed, 19 Dec 2007 08:35:24 +010 |
> 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 Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
> RBS
>
>
> "Martijn Tonies" <m.tonies@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
> >
> >
>
|
| Post Reply
|
| Re: group by cast datetime as integer? |
 |
Wed, 19 Dec 2007 12:53:18 -000 |
re-post as the Google post doesn't seem to get through:
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
"Martijn Tonies" <m.tonies@upscene.removethis.com> wrote in
message
news:4768c98c@newsgroups.borland.com...
>
>> 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 Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>> RBS
>>
>>
>> "Martijn Tonies" <m.tonies@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
>> >
>> >
>>
>
>
|
| Post Reply
|
|
|