RE: Max id
Well, I'm confused.It sounds like your want the max(id) whether or not it's the latest modified.Or are you saying they have the exact same time?Sounds like your database isn't quite what it should be unless I'm missing something here. John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, March 26, 2004 9:59 AM To: CF-Talk Subject: FW: Max id Thanks John. What I need is it to get me the latest record. I setup a primary key in a column named ID. The code below does order by the latest date, but not by the latest date and the latest record. I have three records numbered 1, 3, 4 and it is choosing record three not four which I need. How can the code below be modified to choose the latest date and record number each time? Robert O. -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * >From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
Sorry John. I figured it out. Friday's are tough days to get the brain work oriented. Thanks again for the advice. Robert O. -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 9:13 AM To: CF-Talk Subject: RE: Max id Well, I'm confused.It sounds like your want the max(id) whether or not it's the latest modified.Or are you saying they have the exact same time?Sounds like your database isn't quite what it should be unless I'm missing something here. John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, March 26, 2004 9:59 AM To: CF-Talk Subject: FW: Max id Thanks John. What I need is it to get me the latest record. I setup a primary key in a column named ID. The code below does order by the latest date, but not by the latest date and the latest record. I have three records numbered 1, 3, 4 and it is choosing record three not four which I need. How can the code below be modified to choose the latest date and record number each time? Robert O. -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * >From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
If you want the last record inserted (even if it was yesterday) SELECT TOP 1 side_1_comments FROM status ORDER BY id DESC If you want the last record inserted today SELECT TOP 1 side_1_comments FROM status WHERE datechanged = '#DateFormat(Now(), mm/dd/)#' ORDER BY id DESC This is assuming id is an autonumber Pascal -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: donderdag 25 maart 2004 21:16 To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Max id
At 01:16 PM 3/25/2004, you wrote: I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? You might try cfquery name=status maxrows=1 select side_1_comments from status order by datechanged desc /cfquery That should get the record data with the most recent 'datechanged' date/time. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments, id order by id DESC try that. -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
Couldn't you do: Select top 1 * >From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
sure it would, but it wont give you that ID number that he is looking for... tw -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
*? -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:05 PM To: CF-Talk Subject: RE: Max id sure it would, but it wont give you that ID number that he is looking for... tw -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
It would return the ID as part of the recordset, right, since it a select *?Maybe I'm missing the point. John Burns -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:05 PM To: CF-Talk Subject: RE: Max id sure it would, but it wont give you that ID number that he is looking for... tw -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
nope, I missed the * :) what a Thursday... your right. -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:44 PM To: CF-Talk Subject: RE: Max id It would return the ID as part of the recordset, right, since it a select *? Maybe I'm missing the point. John Burns -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:05 PM To: CF-Talk Subject: RE: Max id sure it would, but it wont give you that ID number that he is looking for... tw -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Max id
Hey, you think your Thursday is bad?You had me thinking I had gone completely nuts :-) John -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:50 PM To: CF-Talk Subject: RE: Max id nope, I missed the * :) what a Thursday... your right. -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:44 PM To: CF-Talk Subject: RE: Max id It would return the ID as part of the recordset, right, since it a select *? Maybe I'm missing the point. John Burns -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:05 PM To: CF-Talk Subject: RE: Max id sure it would, but it wont give you that ID number that he is looking for... tw -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:58 PM To: CF-Talk Subject: RE: Max id Couldn't you do: Select top 1 * From status Order by datechanged desc That will put the newest one on top and return only the top row.Am I missing something? John -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:44 PM To: CF-Talk Subject: RE: Max id Thanks Tony,...only now I get the SQL error below. Robert O. Column name 'status.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:24 PM To: CF-Talk Subject: RE: Max id try this... Select TOP 1 MAX(ID), side_1_comments from status where datechanged = '#DateFormat(Now(), mm/dd/)#' group by side_1_comments order by id DESC tw -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 3:16 PM To: CF-Talk Subject: Max id I have a table that will be updated daily. In the table I want to select info from the latest record even if it is today's date. I used MAX(ID) to get the latest record. Is that good or am I going about this wrong? So far it doesn't seem to do that with my script below: cfquery name=status datasource=status_internal Select MAX(ID), side_1_comments from status Where datechanged = '#DateFormat(Now(), mm/dd/)#' /cfquery Thx. Robert O. (HWW) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
