Hard to say what is going on. The following works fine for me:
SELECT node.userCity,count(node.userCity)
FROM core.users AS node, core.users AS parent
WHERE parent.userName = node.userFirstName
AND node.userCity = 'Pasadena'
GROUP BY node.userCity
I seem to get the correct number based on the no
no luck...
I do need the group by clause, my actual query is much larger than the one
posted. the problem, for example is this:
for five people, paid="no", for five others, paid="yes" under one parent
sponsor.
I am now getting a result of "10", but I need the result to be "5", only
counting
> SELECT COUNT(node.name)
> FROM tbl1 as node, tbl1 as parent
> WHERE parent.sponsor=node.name AND node.paid='yes'
> GROUP BY node.name
GROUP BY should only be used if you're selecting non-aggregates along with
your aggregate.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf S
Well, is the paid field a character field or a number field in the DB?
and just FYI, you can always go #querryName.Recordcount# to get a
count via CF. :)
On Tue, Jun 17, 2008 at 3:23 PM, Jessica Kennedy
<[EMAIL PROTECTED]> wrote:
> I'm pretty sure I will smack my head when I hear the answer, but
daniel kessler wrote:
> I had continued to try and figure it out after I sent the email. I did
> figure out a way to make it work.
>
> FROM expenditures e, people p
> WHERE e.approved_by = people.id (+)
>
> In Oracle, the + does the outer join.
Yes, that is the original outer join syntax for Or
I had continued to try and figure it out after I sent the email. I did figure
out a way to make it work.
FROM expenditures e, people p
WHERE e.approved_by = people.id (+)
In Oracle, the + does the outer join. And it worked well, but it wasn't
explicit. I don't know sql well enough to read th
daniel kessler wrote:
> 1 - I'm not so good at sql and the join only works if there's an id in the
> approved_by field. This is going to be empty unless the ticket has been
> approved, but I still want it to work whether it's approved or not. Is that
> an outter join?
>
Yes it will be an L
>I dont believe this is working with Access DB
> SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound
I think Access's version of CASE is IIF(..). Try using IIF instead and also try
the query Barney suggested. One of them should work.
I dont believe this is working with Access DB
SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound
C S <[EMAIL PROTECTED]> wrote:
>select *, exists (
>select *
>from orders
>where date between #date1# and #date2#
>) as hasOrdered
>from customers
>order by name, id
>
>Use CFQUERYPARAM
>select *, exists (
>select *
>from orders
>where date between #date1# and #date2#
>) as hasOrdered
>from customers
>order by name, id
>
>Use CFQUERYPARAM, of course.
Another variation is a left join. Ditto on using cfqueryparam.
SELECT
c.ID,
c.Name,
c.Email,
SUM(CASE WHEN o.Date IS NULL T
select *, exists (
select *
from orders
where date between #date1# and #date2#
) as hasOrdered
from customers
order by name, id
Use CFQUERYPARAM, of course.
cheers,
barneyb
On Mon, Apr 28, 2008 at 8:57 PM, Brian Sheridan
<[EMAIL PROTECTED]> wrote:
> I will try to make this as simple as possible.
Did that work for you?
On Mon, Mar 10, 2008 at 12:25 PM, Sonny Savage <[EMAIL PROTECTED]>
wrote:
> Now that I understand your data structures, I understand how to solve the
> problem. I hope this works on MySQL. I tested it using OpenOffice.orgBase.
>
> SELECT users.user_id
> , users.user_n
Now that I understand your data structures, I understand how to solve the
problem. I hope this works on MySQL. I tested it using OpenOffice.orgBase.
SELECT users.user_id
, users.user_name
, docs.doc_id
, docs.doc_name
, user_docs.signoff_id
FROM users, docs
LEFT OUTER JOIN us
I think you've made a bit of an error there. With inner joins, the query
returns no results. This is what I expected. Using a left outer join also
returns no rows. This makes sense as you're maintaining the rows from the
wrong table. Changing it to a right outer join creates a cross dependency
erro
This query will return all users and docs that have a relationship
established but don't have a sign-off record:
SELECT u.user_name
, d.doc_name
FROM user_docs ud
INNER JOIN users u
ON ud.user_id = u.user_id
INNER JOIN docs d
ON ud.doc_id = d.doc_id
WHERE ud.signoff_id I
Question: Do you also want a result in the case where there is no USER_DOCS
record for a given USER and DOC record (FULL OUTER JOIN)?
On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote:
> I've got three tables like this (simplified):
>
> USERS
> user_id
> user_name
>
> DOCS
Josh,
The SQL I posted worked for me in my tests. If you deviated from that, can you
post the SQL that you used?
Assuming the join table doesn't have any duplicate rows (and no rows exist in
the join table without a match in both of the other tables) then the resultset
of the query should have
OK, so let's just say that the "where not exists" option is comparable in
efficiency. I ran the query and was greeted by every row in the tables. 70K
results instead of the 60 or so I should be getting.
~|
Adobe® ColdFusion® 8
Josh,
I use WHERE EXISTS and WHERE NOT EXISTS with some frequency and I have never
run into any performance problems with it.
In SQL Server, each join statement needs an "on" clause, even if it is 1 = 1.
I ran some quick tests with about 50 rows in one table, 200 in another, and
about a dozen
>
> In large tables, though, I don't think it's going to be a very good
> solution.
>
Why not? It should perform fine if there are indexes on the tables. Make a
couple of test tables and populate them with a whole load of numbers and
test it before rejecting it ;)
> Anybody know why this doesn't
I'm really looking for a solution with only one query using joins. It should
be possible to do this with a couple of joins.
This query, suggested by Steve, would work well if the tables remained
small:
SELECT user_id,user_name,doc_id,doc_name FROM USERS,DOCS WHERE NOT EXISTS
( SELECT sign
>
> As a side note, I generally recommend that you have a compound primary key
> in a join table. So, USERS_DOCS would have a compound primary key of user_id
> and doc_id and wouldn't need another column. This helps to enforce
> referential integrity.
Or create a unique index / constraint of the
The nice thing about SQL is you can often write the query almost how you word
the problem:
SELECT user_id,user_name,doc_id,doc_name
FROMUSERS,DOCS
WHERE NOT EXISTS (
SELECT signoff_id
FROMUSER_DOCS
WHERE user_id = USERS.user_id
I've never worked in MySQL, but take a look at the below. It should get you
in the right direction. BTW, it's a 2 step process. 1st, your combining
the User and Docs tables to get a list. Then you take that list and bounce
it agaist the USER_DOCS table and show anything where SIGNOFF is NULL.
By the way, this is a MySQL DB.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/
It was pretty close. Running it in "one direction", knowing that table B
has extra records, works fine.
- Original Message -
From: "Dawson, Michael" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Monday, February 11, 2008 7:59 PM
Subject: RE: Query He
bruary 11, 2008 8:45 PM
To: CF-Talk
Subject: RE: Query Help - find unique rows in two similar tables
SELECT
a.*
,b.*
FROM
TableA a
FULL OUTER JOIN TableB b
ON a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NU
SELECT
a.*
,b.*
FROM
TableA a
FULL OUTER JOIN TableB b
ON a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NULL
(Untested)
M!ke
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]
Sent: Monday, February 11, 2008 8:38 PM
change one thing it errors on another lol
now it errors on the ","
>I'll take a look at those
>
>thanks guys
~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/pro
I'll take a look at those
thanks guys
> Looks like CEIL() or CEILING() is the function you're looking for:
> http://db.apache.org/derby/docs/10.2/ref/refderby.pdf
>
> HTH,
> Jon
> On Dec 8, 2007, at 11:18 PM, Dave l wrote:
>
> > if i change the select statement to:
> > SELECT ce
Looks like CEIL() or CEILING() is the function you're looking for:
http://db.apache.org/derby/docs/10.2/ref/refderby.pdf
HTH,
Jon
On Dec 8, 2007, at 11:18 PM, Dave l wrote:
> if i change the select statement to:
> SELECTcenter_id, address1, city, state, postalcode, state,
>
drian
>
> -Original Message-
> From: Dave l
> Sent: 09 December 2007 04:18
> To: CF-Talk
> Subject: Re: query help
>
>
> if i change the select statement to:
> SELECT center_id, address1, city, state, postalcode, state,
>ROUND((ACO
If ROUND isn't there, look for another rounding function like CEILING, FLOOR
etc.
Adrian
-Original Message-
From: Dave l
Sent: 09 December 2007 04:18
To: CF-Talk
Subject: Re: query help
if i change the select statement to:
SELECT center_id, address1, city, state, posta
if i change the select statement to:
SELECT center_id, address1, city, state, postalcode, state,
ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) +
it changes the error to:
'ROUND' is not recognized as a function or procedure.
so i might be sol on this tut
~~
What's the difference between QUERY_RESULTS and GIFTREPORTS?
Do things change if you select over query_results and join giftab1?
-- Andrew
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 02, 2007 3:32 PM
To: CF-Talk
Subject: OT: Query Help
Man, I'm h
Martin
> Thanks for the reply. Yeah I just came up with the same myself as a Q of Qs.
I tried it in the original union query, which would be the ideal solution for me
having it all in the same query, but Oracle throws me an inconsistent data types
error?? Can SUM be used in a union on oracle?
I
Hello Nick.
Thanks for the reply. Yeah I just came up with the same myself as a Q of Qs.
I tried it in the original union query, which would be the ideal solution for
me having it all in the same query, but Oracle throws me an inconsistent data
types error?? Can SUM be used in a union on orac
> The results are great but in some instances a country will appear twice,
> with two different count values, I want the country to appear once with
> the total count of both results.
select COUNTRY, sum(NB_PROFILES) from
(your entire select statement here)
group by COUNTRY
should do it?
Nick
: Jim McAtee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 6 September 2005 11:46 a.m.
To: CF-Talk
Subject: Re: Query Help
Thanks, Mathew. That works. It looks familiar... I remember doing this
before, where it required the use of 'fake' aggregate functions on all
other columns I needed r
at I can't
help thinking either it should be designed differently or that there's
something I'm missing in the design of the database that would allow me to
do this more easily.
- Original Message -
From: "Matthew Walker" <[EMAIL PROTECTED]>
To: "CF-
; Sent: Tuesday, 6 September 2005 10:45 a.m.
> To: CF-Talk
> Subject: Re: Query Help
>
> SELECT DISTINCT p.propertyid, p.name, c.name AS city
> FROM property p
> INNER JOIN property_city pc ON pc.propertyid = p.propertyid
>
> DISTINCT is all you need.
>
> On 9/5/05,
, 6 September 2005 10:45 a.m.
To: CF-Talk
Subject: Re: Query Help
SELECT DISTINCT p.propertyid, p.name, c.name AS city
FROM property p
INNER JOIN property_city pc ON pc.propertyid = p.propertyid
DISTINCT is all you need.
On 9/5/05, Jim McAtee <[EMAIL PROTECTED]> wrote:
> I have a numbe
SELECT p.propertyid, p.name, min(c.name) AS city
FROM property p
INNER JOIN property_city pc ON pc.propertyid = p.propertyid
GROUP BY p.propertyid, p.name
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 6 September 2005 10:31 a.m.
To: CF-Talk
Subject: Query
SELECT DISTINCT p.propertyid, p.name, c.name AS city
FROM property p
INNER JOIN property_city pc ON pc.propertyid = p.propertyid
DISTINCT is all you need.
On 9/5/05, Jim McAtee <[EMAIL PROTECTED]> wrote:
> I have a number of real estate properties, each associated with one or
> more city. If I
If you don't want to do cfqueryparam, use the SQL version. The SQL version
works well if you want to put the query in a folded string that can be
passed to a function that does the cfquery. Data binding is done either
way. In all cases, I think doing the data binding declaration is a best
pra
daniel kessler wrote:
>> On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote:
>>>
>>>AND cs_price <> >> value="">
>>
>> AND cs_price IS NOT NULL
>
> this worked great! thanks!
>
> but why? IOW, why does this work, but these do not:
> AND cs_price <> NULL
> AND cs_price != NULL
Becau
>Cause NULL is not a value per say it would work if it were <> 'NULL'
>assuming NULL is in the column.
well THAT'S gonna be hard to remember. Well maybe this was painful enough to
remember it next time.
thanks for the explanation.
~~~
Cause NULL is not a value per say it would work if it were <> 'NULL'
assuming NULL is in the column.
-Original Message-
From: daniel kessler [mailto:[EMAIL PROTECTED]
Sent: 06 July 2005 12:30
To: CF-Talk
Subject: Re: Query help
>On 7/5/05, Daniel Kessler <[E
>On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote:
>> an empty string. So I've adjusted the query so that if the store =
>> 'convenience' then the price must not equal empt string.
>>
>> AND cs_price <> > value="">
>
>AND cs_price IS NOT NULL
this worked great! thanks!
but why? I
> Personally, I'm not very versed in execution plans, but how
> would the query shown result in 2 execution plans?
There were two queries in my example. Both were identical save for the
literal value of emp.id. I suspect that the methods used by various
databases to determine whether execution pl
Personally, I'm not very versed in execution plans, but how would the query
shown result in 2 execution plans? Where can I read up more about this?
Russ
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 05, 2005 7:53 PM
To: CF-Talk
Subject: RE: Query
> You're right. Maybe the example SQL I gave was not a good one. The
> bottom line is, whenever you have a "static SQL" that does not change
> across your application, using bind variables in that SQL (through
> cfqueryparam) is not necessary. That's my understanding.
I think your understanding is
On 7/5/05, Dave Watts <[EMAIL PROTECTED]> wrote:
> In this particular case, viewed alone, there's no reason to do this.
> However, you might have another query somewhere else like this:
>
> select emp.name, emp.salary
> from emp
> where emp.id = 325
>
> I think this would generally result in two
> Right, but my point was that if you did not pass variables into your
> cfquery, why would you need a cfqueryparam? for example, consider this
> select statement:
>
> select emp.name, emp.salary
> from emp
> where emp.id = 324
>
> would you use a cfqueryparam in the above query? like this:
>
>
er its lifetime.
--
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
"C code. C code run. Run code run. Please!"
- Cynthia Dunning
-Original Message-
From: Eddie [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 05, 2005 4:29 PM
To: CF-Ta
On 7/5/05, Russ <[EMAIL PROTECTED]> wrote:
> I believe SQL (and probably oracle) will cache the execution path if you
> use a cfqueryparam..
Right, but my point was that if you did not pass variables into your
cfquery, why would you need a cfqueryparam? for example, consider this
select statement
I believe SQL (and probably oracle) will cache the execution path if you
use a cfqueryparam..
-Original Message-
From: Eddie [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 05, 2005 5:51 PM
To: CF-Talk
Subject: Re: Query help
On 7/5/05, Jann E. VanOver <[EMAIL PROTECTED]> wrote:
On 7/5/05, Jann E. VanOver <[EMAIL PROTECTED]> wrote:
> AND isNull(cs_price,'') <> value="">
Why would you want to use in this case? since there is
no "query parameter" here, just a literal value ("" or NULL).
--
Eddie.
http://awads.net/
~~
Eddie wrote:
>On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote:
>
>
>>an empty string. So I've adjusted the query so that if the store =
>>'convenience' then the price must not equal empt string.
>>
>>AND cs_price <>
>>
>>
>
>AND cs_price IS NOT NULL
>
>
>
or as I often co
On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote:
> an empty string. So I've adjusted the query so that if the store =
> 'convenience' then the price must not equal empt string.
>
> AND cs_price <>
AND cs_price IS NOT NULL
--
Eddie.
http://awads.net/
~~~
>Have you tried
>
>AND cs_price != ''
I did and then went to be sure it was ok. I found a note saying that it was ok
in some implementations of sql and they recommended the < > so I adjusted to
that with no change. I just tried it again with no change, dangit.
~~
Have you tried
AND cs_price != ''
On 7/5/05 12:57 PM, "Daniel Kessler" <[EMAIL PROTECTED]> wrote:
> I am on Oracle and I'm trying to do a fairly simple query and I just
> don't see the problem with it. I've stared now for way to long.
>
> I am trying to differentiate between grocery store
> It's the first time in some years though that I'm not
> the one responsible for making the guidelines and I'm
> finding it's a welcome relief. I'm sure It'll get old
> at some point, but for now it's much less stressful
> to be the worker bee instead of the manager and main
> dev lead.
I'm tryin
That is an interesting requirement although when I look back I think almost
every system I ever worked with had a column named ID in each table. Here at
work the inhouse framework they make use us, pretty much requires the PK of
a table be just ID and numeric. There are ways around it but they a
Yeah, I'm working with a guy who has, as part of his coding guidelines,
the requirement that every table in the database have a column named ID
that is set as an auto-increment. That's only one of the many fun little
guidelines to which we're strictly adhering. As a developer used to
working al
> Isaac, thanks man, that works too and looks better than my
> case statements. Shame on you for thinking I'd even have
> Access installed on my machine though! It's MySQL.
lol... Well it was a random guess based on the use of now() and "ID"
as a column name because that's real common with Access
Isaac, thanks man, that works too and looks better than my case
statements. Shame on you for thinking I'd even have Access installed on
my machine though! It's MySQL.
--Ferg
S. Isaac Dealey wrote:
>Hey Ken,
>
>I've had those days... try this:
>
>SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS P
Hey Ken,
I've had those days... try this:
SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS PARENT_NAME
FROM ENTITY A
INNER JOIN ENTITY_NAME B ON
(b.entity_id = a.id and now() > b.start_date and
(b.end_date is null or b.end_date > now()))
LEFT JOIN ENTITY_NAME C ON
(c.entity_id = a.parent_id
Below is what I came up with. It seems to work pretty well, but I'd
still welcome any advice on improvement. --Ferg
SELECTdistinct A.ID , B.ENTITY_NAME,
case when A.parent_id is null then '' else C.ENTITY_NAME end AS
PARENT_NAME
FROM ENTITY A, ENTITY_NAME B,
(SELECT entity_na
if ( select count(*) from newsletter where template_name = 'Basic') > 0
update newsletter set template = (param 1) where id =
there is no value here for ID in the where clause.
else
insert into newsletter ( template_name, template ) values ( 'Basic',
(param 2) )
-Ori
"Eric Hoffman" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Monday, January 24, 2005 10:43 AM
Subject: RE: Query Help
> I know its goofybut lets say the 500 lb gorilla wants to eat...and
> he likes crappy reports.
>
> Is there a logic pattern I should follow..
, well, if there
was a payment...count it? I would love to do in a single SQL query...
Know what I mean?
-Original Message-
From: Dina Hess [mailto:[EMAIL PROTECTED]
Sent: Monday, January 24, 2005 10:12 AM
To: CF-Talk
Subject: Re: Query Help
Eric,
I don't think the problem has any
Eric,
I don't think the problem has anything to do with your knowledge of SQL. It's
not typical for a company to allocate order payments to specific order line
items; rather, payments are typically applied to the entire order. Therefore,
how could you possibly determine which line item a paymen
Got it working with this code:
SELECT COUNT(*) as ClientCount
FROM Clients
WHERE
Client_IntakeDate >=
AND
Client_IntakeDate <=
AND
#Trim(Form.Param1Select)# =
AND
#Trim(Form.Param2Select)# =
AND
Okay, I see what you mean but I'm not sure how to define since they
are passed from selects instead of a db field?
Thank you for your help,
~ Donna
On Wed, 03 Nov 2004 01:59:39 +0100, Jochem van Dieten
<[EMAIL PROTECTED]> wrote:
> Donna French wrote:
> > Should this code work if I only pass the
Donna French wrote:
> Should this code work if I only pass the StartDate, EndDate and Param1Val?
No.
>
>
>
>
> username=#MM_wdv_USERNAME# password=#MM_wdv_PASSWORD#>
> SELECT COUNT(Clients.Client_ID) as ClientCount
> FROM Clients
> WHERE
> Client_IntakeDate >= cfsqltype="cf_sql_dat
yes, i can GUARANTEE that.
how about $100 pay via paypal? Talk is cheap.
Once again, I am using oracle 8i.
Max() like count() is a group function.
Determines the largest value in a column.
Nick Han
>>> [EMAIL PROTECTED] 02/17/04 04:21PM >>>
> huh? this statement, select max(date_modified) fr
> huh? this statement, select max(date_modified) from estates,
> will never return more than one record.
>
> Therefore, using '=' is correct.
Can you GUARANTEE that?
If 2 records are modified at exactly the same time, it'll return more
than one record, which will break the "="
[Todays Threads]
And people say that Oracle is better than SQL Server?
I do TOP quite a lot, so it'd be annoying to have to bury my query in a
sub-query that is "unnecessary"
> Intuitively, you would think so, but it doesn't work that
> way. The sql engine would do the rownum first and then apply
> the order-b
3:12 PM
To: CF-Talk
Subject: RE: query help...
> Well, if you do top 1 you would not want dates desc or the first one
> would be the newest (right?).
Oops, I mis-read, the desc was in there as I thought he wanted the
newest
> Other than that, you
> could just return the whole query
Intuitively, you would think so, but it doesn't work that way. The sql engine would do the rownum first and then apply the order-by clause after.
So if you do this, select * from users where rownum >=10 and last_name='SMITH' order by last_name, first_name, you might not get exactly what you exp
AIL PROTECTED] 02/17/04 10:53AM >>>
> > Why not select them ordered by date_modified and then just use the
> > first
> > record? Or if you're worried about passing all of that
> data back and
> > forth you can use TOP (or whatever corresponds to your DB
> Jeremy, I didn't use the rownum example because in the given
> scenario, using rownum may not give you the latest mod date
> record from the all records in the table.
Won't it give you the first record if you specify an ORDER BY?
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsu
> Well, if you do top 1 you would not want dates desc or the
> first one would be the newest (right?).
Oops, I mis-read, the desc was in there as I thought he wanted the
newest
> Other than that, you
> could just return the whole query (if there's no sort of top
> syntax for your DB it'll w
Thanks John, that did it. =)
- Original Message -
From: Burns, John
To: CF-Talk
Sent: Tuesday, February 17, 2004 2:30 PM
Subject: RE: query help...
Well, if you do top 1 you would not want dates desc or the first one
would be the newest (right?). Other than that, you
uot; startrow="1" maxrows="1"> and that would just output the
first record.
John Burns
-Original Message-
From: Daniel Farmer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 2:17 PM
To: CF-Talk
Subject: Re: query help...
I think this has been the close
I think this has been the closest yet... but still no work. =(
- Original Message -
From: Philip Arnold
To: CF-Talk
Sent: Tuesday, February 17, 2004 2:04 PM
Subject: RE: query help...
select top 1 *
from estates
where featured = 1
Order by date_modified desc
OK that sounds like a good solution but how do I select the first record of a query ?
- Original Message -
From: Burns, John
To: CF-Talk
Sent: Tuesday, February 17, 2004 1:53 PM
Subject: RE: query help...
Why not select them ordered by date_modified and then just use the
use the first
record? Or if you're worried about passing all of that data back and
forth you can use TOP (or whatever corresponds to your DB server)
John Burns
-Original Message-
From: Steve Milburn [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 1:51 PM
To: CF-Talk
S
select top 1 *
from estates
where featured = 1
Order by date_modified desc
> From: Daniel Farmer [mailto:[EMAIL PROTECTED]
>
> This is my query...
>
> select * from estates where featured = 1 and
>
> [ I want to select the oldest date_modified field here ]
[Todays Threads]
[This Message]
ECTED]
Sent: Tuesday, February 17, 2004 1:51 PM
To: CF-Talk
Subject: Re: query help...
Daniel Farmer wrote:
> This is my query...
>
> select * from estates where featured = 1 and
>
> [ I want to select the oldest date_modified field here ]
>
>
>
> ~~
Daniel Farmer wrote:
> This is my query...
>
> select * from estates where featured = 1 and
>
> [ I want to select the oldest date_modified field here ]
>
>
>
> ~~
> Daniel Farmer
> Coldfusion Developer / Sales / Producer
> 613.284.1684
> ~~
This didn't work either...
TCX][MyODBC]You have an error in your SQL syntax near
- Original Message -
From: Tony Weeg
To: CF-Talk
Sent: Tuesday, February 17, 2004 1:24 PM
Subject: RE: query help...
select * from estates where featured = 1 and date <= (sele
this didn't work got error... using mySQL btw
TCX][MyODBC]You have an error in your SQL syntax near
- Original MTCX][MyODBC]You have an error in your SQL syntax near essage -
From: Nick Han
To: CF-Talk
Sent: Tuesday, February 17, 2004 1:21 PM
Subject: Re: query
select * from estates where featured = 1 and date <= (select
max(date_modified) from estates where featured = 1)
maybe?
-Original Message-
From: Daniel Farmer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 1:15 PM
To: CF-Talk
Subject: query help...
This is my query...
selec
select * from estates where featured = 1 and date_modified=(select max(date_modified) from estates)
Nick Han
>>> [EMAIL PROTECTED] 02/17/04 10:14AM >>>
This is my query...
select * from estates where featured = 1 and
[ I want to select the oldest date_modified field here ]
> > You may need to use cf_sql_datetime for oracle -- I know
> > mssql server doesn't understand date or datetime, only
> > timestamp...
It doesn't? I always use cf_sql_datetime for dates in SQL Server (I think)
without problems. In fact it always seemed to me that the cfqueryparam
datatypes were
Glad I could help. :)
> Great! That works.
> Thank you,
> James
> -Original Message-
> From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]
> Sent: Sunday, September 21, 2003 2:06 PM
> To: CF-Talk
> Subject: Re: Query help - Selecting record based on date
> As
Great! That works.
Thank you,
James
-Original Message-
From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 21, 2003 2:06 PM
To: CF-Talk
Subject: Re: Query help - Selecting record based on date
Assuming SQL Server or Oracle:
SELECT * FROM MyTable WHERE
Assuming SQL Server or Oracle:
SELECT * FROM MyTable WHERE mydatetimecolumn BETWEEN
and
You may need to use cf_sql_datetime for oracle -- I know
mssql server doesn't understand date or datetime, only
timestamp... blech... but in any event, that should get you
on your way. :)
s. isaac dealey
1 - 100 of 200 matches
Mail list logo
