Selecting row by most recent date

Forum|Forum|17 years ago
August 7, 2008
2 replies
633 views

I'm attempting to select a row where the time ends in :00 and date is the most recent date or just the last record where time ends in :00, either will do.

Once I have that record selected I need to query that query to pull in the previous 3 records which will be times ending in :15, :30 and :45 to round out that full hour of data for processing.

It seems to me that this should be pretty easy but my CF books are not helping me here.

Any Ideas?

Database access

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

Two things I do to make this sort of thing easier are:
1. Take baby steps. Start with one field and the date field. Once you get that to work, add one or two fields at at time.

2. Comma control. This structure helps prevent extra commas from slipping into your query.

select field1
, field2
, date
from yourtable join (
select field1 f1
, field2 f2
, max(datefield) maxdate)
from yourtable
where something
group by field1
, field2
) temp on field1 = f1 etc
where clause

T

The_ScareCrow

Inspiring

You don't indicate what db your using ?

If I was trying to do something like this (I use MS SQL Server) then I would do something like

where dateColumn >= (select top 1 cast(convert(varchar(10), dateColumn, 21) + ' 00:00' as datetime) from tableName)

Ken

D

Dan_Bracuk

Inspiring

This is how you get the record with the most recent date. You can work out the details for your own query.

select field1, field2, datefield
from yourtable join
(select field1 f1, field2 f2, max(datefield) maxdate
group by field1, field2 ) temp
of field1 = f1 and field2 = f2 and datefield = maxdate

I

InkfastAuthor

Inspiring

I've been trying this but all I'm getting is generic error mssg. that doesn't tell me anything helpful. I'll keep working on it.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded