DOH!! end of the day, yeah... cc.cc_type_id isn't part of a group by or
aggregate function..
and I need those end values as part of the returned record set
On 12/13/2012 4:49 PM, John M Bliss wrote:
> This gives you the error...?
>
> CASE
> WHEN cc.cc_type_ID = 1 THEN @careCB + 1
> WHEN cc.cc_ty
Except I need those values individually as part of the return..
On 12/13/2012 4:49 PM, John M Bliss wrote:
> This gives you the error...?
>
> CASE
> WHEN cc.cc_type_ID = 1 THEN @careCB + 1
> WHEN cc.cc_type_ID = 2 THEN @careFBC + 1
> WHEN cc.cc_type_ID = 3 THEN @careSBC + 1
> WHEN cc.cc_type_ID =
This gives you the error...?
CASE
WHEN cc.cc_type_ID = 1 THEN @careCB + 1
WHEN cc.cc_type_ID = 2 THEN @careFBC + 1
WHEN cc.cc_type_ID = 3 THEN @careSBC + 1
WHEN cc.cc_type_ID = 4 THEN @careVACC + 1
WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1
ELSE 0
END AS myvar
On Thu, Dec 13, 2012 at 3:45 PM, Sco
Mike Kear wrote:
> The first two examples are selecting the literal value 'mike' and '1'
> In the first example, you are telling SQL to give the column
> containing 'mike' a name of 'name'.
aha.. literal was the word I was looking for. I did a search for "sql
select literal" and it led me to
I've always known it as selecting a literal value. So "SELECT 1" is "select
the literal value 1".
On Mon, Feb 16, 2009 at 7:41 PM, Mike Soultanian wrote:
>
> I was curious if anyone knows how you describe the following SQL
> functionality:
>
> SELECT 'mike' as name
>
> returns a single column n
> I was curious if anyone knows how you describe the following SQL
> functionality:
>
> ...
>
> The last one is obvious as it's SQL arithmetic, but what are the first
> two examples? Are those also examples of "SQL arithmetic" as well? I
> can't find this kind of SQL functionality described or d
The first two examples are selecting the literal value 'mike' and '1'
In the first example, you are telling SQL to give the column
containing 'mike' a name of 'name'.
A practical example of where you might use this behaviour might be :
SELECT 'Invoice' as doctype, invoiceno, invoicedate, am
Yes, you can do this with a CASE statement. The syntax may depend on
you db, but on SQL Server
ORDER BY
CASE Colleges
WHEN 'Harvard' THEN 1
WHEN 'Princeton' THEN 2
WHEN 'Dartmouth' THEN 3
ELSE 100
END
This would p
Cool. That did the trick. Thanks to all!
-Original Message-
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 2:08 PM
To: CF-Talk
Subject: RE: SQL Question -- Order by a column's value?
Yes, you can do this with a CASE statement. The syntax may depend o
You are correct. Thanks!
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 15, 2008 1:28 PM
To: CF-Talk
Subject: RE: SQL Question, get previous record
I think you'll want an order by on those:
to get the previous:
SELECT TOP 1 idNumber
FROM
If you already have the result set, and it is ordered by the id, then you
could just use this
myQueryResult.id[currentrow+/-1]
to fetch the previous/next id number
very pseudo code here, but hopefully you get the drift.
but not quite sure if this is what you're asking?
On Jan 15, 2008 1:20 PM,
:23 PM
To: CF-Talk
Subject: RE: SQL Question, get previous record
If you are using SQL Server, you can use something like this to get the
previous:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber < #myNumber#
or to get the next:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber > #myNumber#
e-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 15, 2008 1:23 PM
To: CF-Talk
Subject: RE: SQL Question, get previous record
If you are using SQL Server, you can use something like this to get the
previous:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber < #myNumber#
If you are using SQL Server, you can use something like this to get the
previous:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber < #myNumber#
or to get the next:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber > #myNumber#
M!ke
-Original Message-
From: Scott Stewart [mailto:[EMAIL PRO
Mark
i am trying to find where only the lastname was added to the field Contact.
some 2000+ records. then do a match on a known field like email or phone &
then update the Contact field with the combined 'fname lname' from a xls
spread sheet.
then after all the names are combined i will just add 2
ok..
thank you!
SELECT id, Contact, Address, City, State, Zip
FROM Leads
WHERE (Contact LIKE '') OR
(Contact LIKE ' ') OR
(Contact NOT LIKE '% % ')
AND (LTRIM(RTRIM(Contact)) NOT LIKE '% %')
ORDER BY id
now to fix 20,000+ records!
wonde
If what you are trying to do is eliminate trailing spaces why not just do:
Update contacts set contact = rtrim(ltrim(contact))
-Original Message-
From: morchella [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 9:03 AM
To: CF-Talk
Subject: sql question: contains space' '
h
On 1/3/08, Paul Ihrig <[EMAIL PROTECTED]> wrote:
> just fond out why
> man this db is so messed up...
>
> 'Joe Garth '
>
>
> so i would i look for NOT LIKE '% % '
>
OR...
ltrim(rtrim(contact)) NOT LIKE '% %'
~|
Adobe® ColdFusion®
just fond out why
man this db is so messed up...
'Joe Garth '
so i would i look for NOT LIKE '% % '
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclic
You may need to get into database character functions. I believe they
all have them, but they all implement them slightly differently. You
will need to consult appropriate documentation for you database
management system.
But you should be able to do something like this concept.
SELECT field
'% ' shouldnt return ' money'
it should only return enteries with a trailing space..
for example
create table testtbl ( name varchar2(10));
insert into testtbl values ('Greg ');
insert into testtbl values ('Greg M');
insert into testtbl values ('Greg Mo');
insert into testtbl values ('Gary ');
ins
but what if i want
like '% ';
and
not like '% money'
where money could be any last name or character.
On Jan 3, 2008 10:17 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:
> the SQL statement like requires a %..
> for example..
> select * from froo where name like 'G%'
> will return all names that st
the SQL statement like requires a %..
for example..
select * from froo where name like 'G%'
will return all names that starts with G..
So try something like
select * from tbl where name like '% ';
That will catch anything with a trailing space.
Just a heads up..
On Jan 3, 2008 9:03 AM, morchella
> Didn't they mention the table has something like 11 million rows.
Oops. That should have been
"... would be better than a subquery"
Janet
~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax feature
>This should be pretty simple actually =)
>
>
>SELECT DISTINCT
> Order_num,
> datetime_created,
> (SELECT product_name FROM tableName WHERE order_num = a.ordernum
>AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num =
>a.order_num)) as LastProduct
>FROM
> t
This should be pretty simple actually =)
SELECT DISTINCT
Order_num,
datetime_created,
(SELECT product_name FROM tableName WHERE order_num = a.ordernum
AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num =
a.order_num)) as LastProduct
FROM
tableNam
>This solution is similar to Greg's in that I will get dupes if more than
>one product is added at the same time.
Sql 2005 may have a better method for doing this, but you could use 2 derived
tables. One to grab the max date by order number, and the other to grab the max
record id per order numb
Friday, September 28, 2007 11:58 AM
To: CF-Talk
Subject: RE: SQL Question
Just move the aggregate up to the from and do a join:
select *
from t myT, (select order_num, max(datetime_created) as max_dt_created
from t group by order_num)
where myT.order_num = .order_num
AND
>Have you tried using a derived table? I think that should work.
I think it should work too, assuming there would _not_ be duplicate
datetime_created values per order_num. If there were, the query could return
multiple rows per order_num.
Janet
~~
You might want to post this at [EMAIL PROTECTED] There are some
sharp SQL experts there.
Bruce
Brad Wood wrote:
> Yes, I did several attempts at a derived table, but I still ran into the
> same problem... I couldn't do a top 1 with order by datetime_created
> desc because I am reporting across
;t get
the product_name, and if I group by the product_name as well, it
itemizes all the products.
~Brad
-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:54 AM
To: CF-Talk
Subject: RE: SQL Question
Have you tried using a derived table? I
have to determine the max
datetime_create, so you're going to have to use a subselect I think.
-- Andrew
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 12:46 PM
To: CF-Talk
Subject: RE: SQL Question
Doesn't look like MS SQL Server
-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:46 AM
To: CF-Talk
Subject: RE: SQL Question
Doesn't look like MS SQL Server 2005 will let me compare more than one
column in a where clause.
Even if it did, this requires two selects. My understanding is th
at had the max value would work.
~Brad
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:46 AM
To: CF-Talk
Subject: RE: SQL Question
Doesn't look like MS SQL Server 2005 will let me compare more than one
al Message-
From: Andrew Clark [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:33 AM
To: CF-Talk
Subject: RE: SQL Question
Off the top of my head I get:
select *
from t myT
where (order_num, datetime_created) =
(select order_num, max(datetime_created) from t where
Off the top of my head I get:
select *
from t myT
where (order_num, datetime_created) =
(select order_num, max(datetime_created) from t where order_num
= myT.order_num group by order_num)
Note:
I tested this in postgres, not sql server...
-- Andrew
-Original Message-
From:
There are a number of ways to do this. The typical way that I get this done is
(im going to use your example of rows 31-50 for a total of 20 rows)
SELECT TOP 20 *
FROM [tableName]
WHERE [primaryKeyField] not in (
SELECT top 30 [primaryKeyField]
FROM [tableName]
WHERE [criteriaField] =
:00 AM
To: CF-Talk
Subject: RE: SQL question
Thanks Ben, and Joe here is what finally worked for me.
dateCreated >= '04/03/2007' AND dateCreated < DATEADD(DAY, 1,
'04/03/2007')
With
dateCreated >= '04/03/2007' AND dateCreated < ('04/03/2007&
en converting the varchar value '04/03/2007' to data type
int.
So I probably have to cast as a date in order to get this to work.
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:48 AM
To: CF-Talk
Subject: Re: SQL question
Chad
?
www.bennadel.com/ask-ben/
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:46 AM
To: CF-Talk
Subject: RE: SQL question
I just tried this and I get no records.
dateCreated >= 04/02/2007 AND dateCreated < (04/02/2007 + 1)
Is th
thod compatible with MS SQL?
>
>
>
>
> -Original Message-
> From: Chad Gray [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 03, 2007 10:41 AM
> To: CF-Talk
> Subject: RE: SQL question
>
> On the (@date + 1) how do you know it is adding one day?
>
>
Subject: RE: SQL question
On the (@date + 1) how do you know it is adding one day?
Out of curiosity how do you add one year?
Thanks for the clean elegant solution. I will try it out.
~|
Deploy Web Applications Quickly across the
u add one year?
>
> Thanks for the clean elegant solution. I will try it out.
>
>
> -Original Message-
> From: Ben Nadel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 03, 2007 10:26 AM
> To: CF-Talk
> Subject: RE: SQL question
>
> People people people :)
I just tried this and I get no records.
dateCreated >= 04/02/2007 AND dateCreated < (04/02/2007 + 1)
Is this method compatible with MS SQL?
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:41 AM
To: CF-Talk
Subject: RE: SQL questi
: SQL question
People people people :)
I have seen casting, converting, date-diffing, LIKE'ing,
MONTH()/Day()/Year()'ing Please do not run functions on your
date/time fields. Running a function on a column in general is extremely
slow. Date/time stamps can be used quite nicely wit
Ah Gotcha.
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:35 AM
To: CF-Talk
Subject: RE: SQL question
BETWEEN is good, but it is doubly-inclusive meaning that it is like doing
both >= and <=. In this case, it might turn up r
d comparison).
..
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-Original Message-
From: Mark A Kruger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:30 AM
To: CF-Talk
Subject: RE: SQL question
Ben,
Ok...
Ben,
Ok... Nicely done. What about "BETWEEN" ... Any benefits there?
WHERE date_created BETWEEN @date AND @date + 1
-Mark
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:26 AM
To: CF-Talk
Subject: RE: SQL question
People peo
People people people :)
I have seen casting, converting, date-diffing, LIKE'ing,
MONTH()/Day()/Year()'ing Please do not run functions on your
date/time fields. Running a function on a column in general is extremely
slow. Date/time stamps can be used quite nicely with out them:
DECLARE @date D
The trick with date and MSSQL is using > and <. The string '04/02/2007'
is seen by MSSQL as '04/02/2007 00:00:00', so your condition will return
only those records with that exact timestamp. You have to use:
WHERE dateCreated >= '04/02/2007'
AND dateCreateted < '04/03/2007'
Steve Brownlee
http
#
AND
MONTH(dateCol)=#monthVar#
AND
YEAR(dateCol)=#yearVal#
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:12 AM
To: CF-Talk
Subject: RE: SQL question
I tried that too and no records are returned.
-Original Message-
From: Che Vil
AH! This works!
Thanks!
-Original Message-
From: Paul Hastings [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:10 AM
To: CF-Talk
Subject: Re: SQL question
Chad Gray wrote:
> I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/
Paul Hastings [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:10 AM
To: CF-Talk
Subject: Re: SQL question
Chad Gray wrote:
> I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreat
Ooh.. I like that one
-Original Message-
From: Paul Hastings [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:10 AM
To: CF-Talk
Subject: Re: SQL question
Chad Gray wrote:
> I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/
; I tried that also and no records are returned.
>
>
> -Original Message-
> From: Che Vilnonis [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 03, 2007 10:01 AM
> To: CF-Talk
> Subject: RE: SQL question
>
> How about you simply use WHERE dateCreated = '4/2/
I tried that too and no records are returned.
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:09 AM
To: CF-Talk
Subject: RE: SQL question
How about using...
WHERE dateCreated LIKE '%4/2/2007%'
-Original Message-
From:
Chad Gray wrote:
> I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0
~|
Create Web Applications With ColdFusion MX7 & Flex 2.
Build powerful, scalable RIAs. Free Trial
http://www.ado
How about using...
WHERE dateCreated LIKE '%4/2/2007%'
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:05 AM
To: CF-Talk
Subject: RE: SQL question
I tried that also and no records are returned.
-Original Message-
I tried that also and no records are returned.
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:01 AM
To: CF-Talk
Subject: RE: SQL question
How about you simply use WHERE dateCreated = '4/2/2007'
Basically, lose the padding ze
I wrote a blog post concerning this a while back:
http://www.stillnetstudios.com/2007/01/20/comparing-dates-without-times-in-sql-server/
Hope that helps.
-Ryan
Chad Gray wrote:
> I am using MS SQL and have a field with data type DateTime.
>
> I want to find all records with the day 4/2/2007?
>
>
How about you simply use WHERE dateCreated = '4/2/2007'
Basically, lose the padding zeros.
~Che
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:59 AM
To: CF-Talk
Subject: SQL question
I am using MS SQL and have a field with data type DateTi
On what engine? If this is MSSQL, try running the query tuning advisor.
There maybe some updates to indexes or statistics that will speed it up.
-Original Message-
From: Jerry Barnes [mailto:[EMAIL PROTECTED]
Sent: Monday, April 02, 2007 12:22 PM
To: CF-Talk
Subject: OT: SQL Question
Th
Wold moving the M.i_recid IS NULL to the JOIN help?
SELECT
F.pid,
F.acrostic,
F.recid,
F.recordthread,
F.aed_onset,
F.d_form
FROM
vfrm_sae F
LEFT OUTER JOIN
v_sae_jna_mr M
ON
F.recordthread = M.i_recordThread AND M.i_recid
Doug Brown wrote:
> Ok, so if my data will only be supporting the English language I should just
> use varhcar or char since n uses 2 bytes for one character. Correct?
never say "never". unless you're going to be dealing in TB of data, better safe
than sorry.
Doug Brown wrote:
> I understand several things about SQL when it comes to getting information
> out of it, but never really have understood which data types to use for what
> specific data. I know what ones suppose to hold what kind of data as far as
> integer data, character data, monetary data,
rsday, March 29, 2007 4:22 PM
To: CF-Talk
Subject: RE: SQL Question?
I believe so...
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2007 4:03 PM
To: CF-Talk
Subject: RE: SQL Question?
Ok, so if my data will only be supporting the English language I s
I believe so...
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2007 4:03 PM
To: CF-Talk
Subject: RE: SQL Question?
Ok, so if my data will only be supporting the English language I should just
use varhcar or char since n uses 2 bytes for one
: SQL Question?
I've always read that you use nvarchar for multilingual data. Keep in mind,
nvarchar takes up twice as much space in the db since it makes an alotment
for languages that have extended characters.
~C
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thu
I've always read that you use nvarchar for multilingual data. Keep in mind,
nvarchar takes up twice as much space in the db since it makes an alotment
for languages that have extended characters.
~C
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2
Damn! Always the simple shit that gets me.
Thanks.
On 2/1/07, Jochem van Dieten <[EMAIL PROTECTED]> wrote:
>
> Bruce Sorge wrote:
> > OK, I figured it out pretty much. So now I have this:
> >
> > CREATE TABLE #tempduplicatedata
> > (
> > Code NVARCHAR(20)
> > )
> >
> >
> > --Identify and save dup
Bruce Sorge wrote:
> OK, I figured it out pretty much. So now I have this:
>
> CREATE TABLE #tempduplicatedata
> (
> Code NVARCHAR(20)
> )
>
>
> --Identify and save dup data into temp table
> INSERT INTO #tempduplicatedata
INSERT INTO #tempduplicatedata (code)
> SELECT Code FROM Codes
> GROUP
You may want to recompile the sp... It may have been compiled before the
"statistics" for the tables involved were updated.
You could check the query plan of the sp vs. the query to see how they
differ. (Not sure what db you are using...)
Mark
-Original Message-
From: Tim Do [ma
How many records are coming back?
How are you calling it?
I have ran tests before for where running exec sp_name in side of a
cfquery was faster than cfstoredproc.
Just a thought.
Also are you sure the stored proc is really taking 20 seconds to run OR
is the CF page just taking 17 seconds to pr
Bruce Sorge wrote:
> I have a lot of duplicate information in a table. I know how to query to
> find the dupes, but I am having problems with deleting them (there are
> thousands). I tried this:
create a clone of your table but make your "key" duplicated column as a unique
key setting the index t
I actually bloged about this earlier this month. See here:
http://www.ruslansivak.com/index.cfm/2007/1/10/Deleting-duplicate-rows-from-
SQL-Server
Russ
> -Original Message-
> From: Bruce Sorge [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 12:26 PM
> To: CF-Talk
> Subject
OK, I figured it out pretty much. So now I have this:
CREATE TABLE #tempduplicatedata
(
Code NVARCHAR(20)
)
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT Code FROM Codes
GROUP BY Code
HAVING COUNT(Code) > 1
--Confirm number of dup rows
SELECT @@ROWCOUNT AS '
Bruce Sorge wrote:
> I have a lot of duplicate information in a table. I know how to query to
> find the dupes, but I am having problems with deleting them (there are
> thousands). I tried this:
>
> DELETE
> FROM CODES
> WHERE Code =
> (SELECT Code,
> COUNT(Code) AS NumOccurrences
> FROM Codes
> G
Without looking too hard, would using a TOP 1 in the sub select work?
-Original Message-
From: Bruce Sorge [mailto:[EMAIL PROTECTED]
Sent: 01 February 2007 17:26
To: CF-Talk
Subject: SQL QUestion
I have a lot of duplicate information in a table. I know how to query to
find the dupes, but
Bruce,
One way to accomplish this is to query your (unique) records and populate
another table with the same structure with that data. Once it is done, then
you can re-populate that table from the table you created. Hope that makes
sense.
Doug B.
- Original Message -
From: "Bruce Sorg
That looks like the right/only way to do it as far as I know.
Mark
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 30, 2006 11:02 AM
To: CF-Talk
Subject: OT: SQL question
I am not a MS SQL guru and this bit of SQL is about as advanced as I
g
You could also do:
SELECT MAX(thedate)
FROM yourtable
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 22, 2006 9:05 AM
To: CF-Talk
Subject: SQL Question
Im a little burnt out need some help.
If I query a table that tracks the number of times a
Thanks Kris! That makes sense.
> -Original Message-
> From: Kris Jones [mailto:[EMAIL PROTECTED]
> Sent: Sunday, October 22, 2006 10:12 AM
> To: CF-Talk
> Subject: Re: SQL Question
>
> select top 1 *
> from tablename
> order by datefield desc
>
>
select top 1 *
from tablename
order by datefield desc
On 10/22/06, Chad Gray <[EMAIL PROTECTED]> wrote:
> Im a little burnt out need some help.
>
> If I query a table that tracks the number of times a book was checked out and
> returned. I want to get the most recent item in the table to f
Thank you all for your suggestions, after half a day of hair pulling, I figured
out what's going on.. actually the database table given to me wasn't
consistent. There really were no listings for some folders. That was the
reason...
Otherwise to achieve what I wanted, this will work:
select fil
Thank you all for your suggestions, after half a day of hair pulling, I figured
out what's going on.. actually the database table given to me wasn't
consistent. There really were no listings for sme folders. that was the
reason...
Otherwise to achieve what I wanted, this will work:
select file
Agreeing with everyone who has already posted, this might not be the best
place to be doing this... But one more option to play with:
WHERE
[field] LIKE '_%\_%'
AND
[field] NOT LIKE '%.__'
AND
[field] NOT LIKE '%.___'
AND
[field] NOT LIKE '%.'
..
o Hagen
To: CF-Talk
Sent: Wed Aug 02 21:56:24 2006
Subject: Re: sql question.
SQL Server sucks at regexps, you have some rudimentary tools at your
disposal with the LIKE statement, but none that I could get to work, I
have in the past found a regexp stored procedure but that's just dog slow.
select *
>From tablename
where patindex('%\%\%',dir) = 0
And patindex('%\%',dir) > 0
-Original Message-
From: Brian Dumbledore [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 02, 2006 4:31 PM
To: CF-Talk
Subject: sql question.
I am using MS-SQL, I couldn't get this to work..
I trie
On 8/2/06, Brian Dumbledore <[EMAIL PROTECTED]> wrote:
> I am using MS-SQL, I couldn't get this to work..
>
> I tried, patindex,charindex, like combinations, none worked.
>
> I have directory paths in a table, given a starting path of a directory, I
> want to get its first level elements.
>
> eg:
SQL Server sucks at regexps, you have some rudimentary tools at your
disposal with the LIKE statement, but none that I could get to work, I
have in the past found a regexp stored procedure but that's just dog slow...
This is the like statement I tried:
SELECT * FROM listing
WHERE dir LIKE
On Monday 12 June 2006 15:54, Ben Nadel wrote:
> But it might be better to make something with more feedback:
Or use two queries and a transaction
SELECT MAX(StatusWhen) as maxDate FROM table
UPDATE table
SET STATUS = 'approved',
WHERE SKU = http://www.houseoffusion.com/lists.cfm/link=i:4:2432
Thanks for the answers... they all appear to do what I need.
Thanks again,
Chad
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243204
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http
Chad,
You can do a sub query:
UPDATE table
SET STATUS = 'approved',
WHERE SKU = #URL.SKU# AND StatusWhen =
(
SELECT MAX(StatusWhen) FROM table
)
But it might be better to make something with more feedback:
// Declare the ID to be updated
DECLARE @id INT;
// Get the ID to be updated
Chad Gray wrote:
> How would I write the SQL to update a record that has the most recent date?
>
> Say I want to change the status field to "Approved" where the date is the
> most recent and SKU = 12345
>
> Will the Max function work in the Where section of the SQL or is there
> another functio
UPDATE
tablename
SET
status = 'approved'
WHERE
SKU = #URL.SKU# AND
statusWhen = (SELECT MAX(statusWhen) FROM tablename)
i think that should do it...
(to answer the actual question, I'm not sure if the MAX() function
will work as you originally asked)
On 6/12/06, Chad Gray <[E
use a subquery
update table set status = 'approved'
where sku =
and statuswhen = (
select max(statuswhen)
from status
where ... --include where clauses
)
On 6/12/06, Chad Gray <[EMAIL PROTECTED]> wrote:
> How would I write the SQL to update a record that has the most recent date?
>
> Say I want
a cleaner way of writing this might be:
INSERT INTO hourly
(employee_id
,ticket_no
,ticket_date
,labor_start
,labor_stop
,labor_lunch
,truck_id
,equip_start
,equip_stop
,equip_down
,equip_idle)
VALUES
No problem.
On 4/19/06, David Elliott <[EMAIL PROTECTED]> wrote:
> Thanks Ryan.
>
> Ryan Guill <[EMAIL PROTECTED]> wrote: On 4/19/06, David Elliott wrote:
> > Below is a part of my code
>
> >
> > And my question might be a simple onethe line that says
> > ,equip_idle...does this mean I'l
Dave,
I am not sure I follow 100%, but yes, the CFIF statements will stop the
values from being added. However, the column will still be in the database
for that new record and will have whatever default value you have assigned
to that column (or NULL if no default value has been set and the colum
Thanks Ryan.
Ryan Guill <[EMAIL PROTECTED]> wrote: On 4/19/06, David Elliott wrote:
> Below is a part of my code
>
> And my question might be a simple onethe line that says
> ,equip_idle...does this mean I'll only have any entry if their is a value in
> it?
Yes, but a better way I thi
1 - 100 of 528 matches
Mail list logo
