RE: HELP: Retrieving 5 most recent records by date...

[Chris Chambers]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

SELECT  TOP 5 field
FROMtablename
ORDER BYdatefield

_

Chris Chambers

ICQ: 40311211
AIM: CTC HOME

VOX: 757-896-6393
FAX: 757-896-0774 
_

- -Original Message-
From: Dave Notik [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2000 10:34 PM
To: ColdFusion List
Subject: HELP: Retrieving 5 most recent records by date...
Importance: High


All:

I've done this many times in the past, but am now arguing the most
efficient
way to handle querying a database and retrieving the 5 (or however
many)
most recent records, as defined by date.

E.g. say I have a database in which I insert articles.  I have a
DateEntered
field which has a smalldatetime datatype.  I want to list the most
recent
first, second most recent, third most recent, etc...

Is there an "efficient" way of doing this or do I have to select all,
order
by the DateEntered field, loop over the results, etc?  Any help or
suggestions is appreciated as to what is the best method for handling
this
case.

- -Dave

- --
- 
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_tal
k or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.

-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.5.3 for non-commercial use 

iQA/AwUBOP0fUkejih2dfV0dEQJdzwCgsFDEtITgjE6/6wObVIha5T8W2wAAoIkY
mVzg5F/mFV1TzQv2ix/ZIiMf
=yFeJ
-END PGP SIGNATURE-

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: HELP: Retrieving 5 most recent records by date...

[Dave Notik]

Thanks, Chris.  That confirmed what we were doing.  I knew there was a TOP
keyword and I'd even used it before but for some reason it wasn't working -
we were doing TOP(5) for some strange reason.

Thanks again, Chris.

-Dave

-Original Message-
From: Chris Chambers [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2000 7:52 PM
To: [EMAIL PROTECTED]
Subject: RE: HELP: Retrieving 5 most recent records by date...


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

SELECT  TOP 5 field
FROMtablename
ORDER BYdatefield

_

Chris Chambers

ICQ: 40311211
AIM: CTC HOME

VOX: 757-896-6393
FAX: 757-896-0774
_

- -Original Message-
From: Dave Notik [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2000 10:34 PM
To: ColdFusion List
Subject: HELP: Retrieving 5 most recent records by date...
Importance: High


All:

I've done this many times in the past, but am now arguing the most
efficient
way to handle querying a database and retrieving the 5 (or however
many)
most recent records, as defined by date.

E.g. say I have a database in which I insert articles.  I have a
DateEntered
field which has a smalldatetime datatype.  I want to list the most
recent
first, second most recent, third most recent, etc...

Is there an "efficient" way of doing this or do I have to select all,
order
by the DateEntered field, loop over the results, etc?  Any help or
suggestions is appreciated as to what is the best method for handling
this
case.

- -Dave

- --
- 
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_tal
k or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.

-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBOP0fUkejih2dfV0dEQJdzwCgsFDEtITgjE6/6wObVIha5T8W2wAAoIkY
mVzg5F/mFV1TzQv2ix/ZIiMf
=yFeJ
-END PGP SIGNATURE-


--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: HELP: Retrieving 5 most recent records by date...

[Dan O'Keefe]

Don't you need to add the DESC argument to the order by to achieve the
desired results?

Dan

-Original Message-
From: Chris Chambers [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2000 9:52 PM
To: [EMAIL PROTECTED]
Subject: RE: HELP: Retrieving 5 most recent records by date...


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

SELECT  TOP 5 field
FROMtablename
ORDER BYdatefield

_

Chris Chambers

ICQ: 40311211
AIM: CTC HOME

VOX: 757-896-6393
FAX: 757-896-0774
_

- -Original Message-
From: Dave Notik [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2000 10:34 PM
To: ColdFusion List
Subject: HELP: Retrieving 5 most recent records by date...
Importance: High


All:

I've done this many times in the past, but am now arguing the most
efficient
way to handle querying a database and retrieving the 5 (or however
many)
most recent records, as defined by date.

E.g. say I have a database in which I insert articles.  I have a
DateEntered
field which has a smalldatetime datatype.  I want to list the most
recent
first, second most recent, third most recent, etc...

Is there an "efficient" way of doing this or do I have to select all,
order
by the DateEntered field, loop over the results, etc?  Any help or
suggestions is appreciated as to what is the best method for handling
this
case.

- -Dave

- --
- 
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_tal
k or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.

-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBOP0fUkejih2dfV0dEQJdzwCgsFDEtITgjE6/6wObVIha5T8W2wAAoIkY
mVzg5F/mFV1TzQv2ix/ZIiMf
=yFeJ
-END PGP SIGNATURE-


--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Re: HELP: Retrieving 5 most recent records by date...

[Dana Larose]

This works, but SQL will select the top 5 dates, not records so if have more
than one record with the same date, you could get more than just 5 records
returned.

If you have unique dates it won't be a problem, but otherwise you may still
have to toss the results into an array (although it will be much smaller
than if you had selected all the records)

Dana


--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.