Just read the original post properly - please ignore me.
Dominic
2009/11/15 Dominic Watson
> Depending on what you are doing with this data, seems to me that this
> should be done in the front end and not the db. So get your data in the
> original format:
>
> USER, CODE
>
> rick,AL
> rick,FR
>
Depending on what you are doing with this data, seems to me that this should
be done in the front end and not the db. So get your data in the original
format:
USER, CODE
rick,AL
rick,FR
rick,TR
rick,HS
joe,AL
joe,FU
Bob,FM
And then use cfoutput with query and group (a very rough output here):
You wrote a pivot query without using pivot. BTW, the aggregate for the pivot
query can be Count().
-Original Message-
From: Rick Root [mailto:rick.r...@gmail.com]
Sent: Friday, November 13, 2009 4:19 PM
To: cf-talk
Subject: Re: (ot) SQL Question - flattening data
>From
>From the documentation, pivot tables seem to require aggregate
functions... The generic description would seem to work but the
examples make it difficult to see how.
But... I figured out a solution! Using SQL Server's row_number() over
(partition by XXX order by XXX) I can make a subquery that
]
Sent: Friday, November 13, 2009 1:11 PM
To: cf-talk
Subject: RE: (ot) SQL Question - flattening data
Is there a particular reason to return them in this format? I would
think that the straight query output would be simpler to work with.
However, you can accomplish this either by using cursors to
Is there a particular reason to return them in this format? I would think that
the straight query output would be simpler to work with. However, you can
accomplish this either by using cursors to loop over the query output and build
what you are looking for or by building a crosstab query of t
Mark/Dave... thanks so much. That worked perfectly!
-Original Message-
From: Mark Kruger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2008 10:19 AM
To: CF-Talk
Subject: RE: (ot) SQL question...
Che,
Well you could it inline... something like
Select sum(t.total) as total
Che,
Well you could it inline... something like
Select sum(t.total) as total, t.source
FROM
(
select count(*) as total, source
from listings
group by source
union all
select count(*) as total, source
from speclistings
group by source
union all
select count(*) as total, source
from psportl
Che,
Try this:
SELECT count(total) as sourcetotal, source
FROM (
select count(*) as total, source
from listings
group by source
union all
select count(*) as total, source
from speclistings
group by source
union all
select count(*) as total, source
from psportlistings
group by source
)
GROUP B
I actually prefer to do this in the ORDER BY clause (keeping the ordering
logic in the ORDER BY instead of in the SELECT) but the end result is the
same. If you won't or can't add a sort column to the table, a CASE statement
is about the only other way to do this in the query itself.
On Jan 25, 20
Che Vilnonis wrote:
> I was trying to do that w/o adding another column. Can it be done?
Yes, see Crow's, Charlie's or my post on using CASE to create an inline
sort column with SQL.
~|
Adobe® ColdFusion® 8 software 8 is the m
riday, January 25, 2008 12:55 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?
Do you mean put them in a predifind order based on the college, other
than alphabetical?
If so, and if you have a lookup table for your colleges, you will have
to add a numerical column calle
There is no way to do it with pure SQL alone.
Dominic
Well actually you can do in pure SQL. SQL has code that can be used to create
dynamic columns and values on the fly in your record set and then one can order
on this set. It sort of depends on whether the desire order is permanent or
flex
Yes, you can do this using case statements in your order by:
example:
select * from viewoffers where [EMAIL PROTECTED]
order by
case status
when 'active' then 1
when 'rejected' then 2
else 99
end
Of course, this is really a kludge. The DB should be deisgned a little
better, but sometime
Che Vilnonis wrote:
> Suppose I have a small set of data with a column named "Colleges". Is there
> a way to write an ORDER BY statement to say something like...
> ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???
>
> Just wondering... Che
If I understand your question cor
something like...
SELECT
Colleges,
CASE
when Colleges = 'Harvard' THEN 1
when Colleges = 'Princeton' THEN 2
when Colleges = 'Dartmouth' THEN 3
END AS collegeOrder
FROM
myTable
ORDER BY
collegeOrder
(not tested) :)
On Jan 25, 2008 10:41 AM, C
I was trying to do that w/o adding another column. Can it be done?
-Original Message-
From: Todd [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 1:50 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?
Nope, add a sort_order column and sort your col
Sorry, add sort_order column and then do an ORDER BY sort_order and set all
the colleges in the appropriate sorting that you want it to be.
On Jan 25, 2008 1:49 PM, Todd <[EMAIL PROTECTED]> wrote:
>
> Nope, add a sort_order column and sort your colleges appropriately.
>
>
> On Jan 25, 2008 1:41 P
Do you mean put them in a predifind order based on the college, other than
alphabetical?
If so, and if you have a lookup table for your colleges, you will have to
add a numerical column called 'Ordinal' (or something else) with which you
can set their order. Then simply order by that in your SQL s
Nope, add a sort_order column and sort your colleges appropriately.
On Jan 25, 2008 1:41 PM, Che Vilnonis <[EMAIL PROTECTED]> wrote:
> Suppose I have a small set of data with a column named "Colleges". Is
> there
> a way to write an ORDER BY statement to say something like...
> ORDER BY Colleges
Thanks for your replies.
I didn't get a chance to play with anything this afternoon due to
meetings. I'll try to implement something tommorrow and fill you all
in on the results.
The recordsets aren't that big. About 9k records in one table and 3k
in the other.
Jerry Barnes wrote:
> The following query is slow. I'd like to speed it up a bit. Any
> suggestions would be appreciated.
>
>
> SELECT
>F.pid,
>F.acrostic,
>F.recid,
>F.recordthread,
>F.aed_onset,
>F.d_form
> FROM
>vfrm
Has the i_recid field in the v_sae_jna_mr table been indexed? If your
table is very large, this could slow your performance.
Dean
--
__
Dean Lawrence, CIO/Partner
Internet Data Technology
888.GET.IDT1 ext. 701 * fax: 888.438.4381
http://www.idatatech.com/
C
LAN?
I think the sql statement you have is just about as straight forward as you
are going to get. how long does a straight SELECT * FROM F INNER JOIN M
taking??
-Original Message-
From: Kris Jones [mailto:[EMAIL PROTECTED]
Sent: Monday, April 02, 2007 11:43
To: CF-Talk
Subject: Re: OT
How about something like this:
SELECT
F.pid,
F.acrostic,
F.recid,
F.recordthread,
F.aed_onset,
F.d_form
FROM
vfrm_sae F
WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread)
Not sure it'll be much faster, but it's worth a try.
Cheers,
Kris
> The following
Chad Gray wrote:
>
> I am basically doing a query on a table of catalogs and elements
> inside of the catalog. Each element has a history of status changes
> (new, in progress, done etc).
So each element always has a corresponding value in the history table.
> SELECT c.CatalogID, c.CatalogNa
danke!
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 08, 2005 2:12 PM
To: CF-Talk
Subject: Re: OT SQL question
Eric Creese wrote:
> I want to verify email addresses that are entered into one of my apps.
> Unfortunately I already inh
Eric Creese wrote:
> I want to verify email addresses that are entered into one of my apps.
> Unfortunately I already inherited close to 100k email address. So I want to
> do the following in SQL via a stored procedure so I can write the bad
> addresses out to an error table. Need to check if th
Thanks but I also need to try to test the TDLs like .com, .net, .uk...
-Original Message-
From: Qasim Rasheed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 08, 2005 1:19 PM
To: CF-Talk
Subject: Re: OT SQL question
I think you can write a UDF to validate email addresses. Here is
I think you can write a UDF to validate email addresses. Here is link
http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm
On Tue, 8 Feb 2005 12:55:42 -0600, Eric Creese <[EMAIL PROTECTED]> wrote:
> I want to verify email addresses that are entered into one of my apps.
> Unfortu
Well.. oneway to do it is to create a new relationship table.. i.e
tblRelatedTrails
> relationID
> trailID
> relatedTrailID
and when doing a select for review you can do a sub select on the
relatedTrails table.. and use IN()
anotherway is to use the geo info of the trails.. and sele
From: Umer Farooq [mailto:[EMAIL PROTECTED]
Sent: Fri 2/4/2005 12:54 PM
To: CF-Talk
Subject: Re: OT: Sql question
John Munyan wrote:
> I have a question about how a relationship would be best modeled in SQL.
> Currently I have a hiking website, which hosts trail r
John Munyan wrote:
> I have a question about how a relationship would be best modeled in SQL.
> Currently I have a hiking website, which hosts trail reviews. People can add
> their own comments which are associated with the hike.
>
> For instance maybe I hiked snow lake on 12/1/05 and also 6/
yeah, i figured that, i made sep. dsn's and its all good now :)
thanks.
tony
On Wed, 22 Sep 2004 15:21:53 -0400, Qasim Rasheed
<[EMAIL PROTECTED]> wrote:
> As far as I know you cannot.
>
>
>
>
> - Original Message -
> From: Tony Weeg <[EMAIL PROTECTED]>
> Date: Wed, 22 Sep 2004 09:59:
As far as I know you cannot.
- Original Message -
From: Tony Weeg <[EMAIL PROTECTED]>
Date: Wed, 22 Sep 2004 09:59:03 -0400
Subject: ot: sql question
To: CF-Talk <[EMAIL PROTECTED]>
is it true that we cannot use go in sql statements using cfquery?
--
tony
Tony Weeg
macromedia cert
ieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 11:10 AM
To: CF-Talk
Subject: Re: ot: sql question
Tony Weeg wrote:
> how would I use HAVING to select out where the difference between
> reportsInDatabase and DistinctTimes is above 15%
> select r.IpAddressNumb
Tony Weeg wrote:
> how would I use HAVING to select out where the difference between
> reportsInDatabase and DistinctTimes is above 15%
> select r.IpAddressNumber, v.VehicleIp,
> Count(r.ReportId) as ReportsInDatabase,
> Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName
> fro
> I have a ColdFusion app that is dynamically managing a SQL Server 2K
> database. What I am running into is the need to change a column
> data type - specifically an ntext data type. Anybody got any
> suggestions?
>
> It appears that I cannot used ALTER TABLE/COLUMN with an ntext data
> type,
> Can someone assist me with a quick SQL statement.
>
> I have a table with 3 columns: A, B, C (A would be the primary key)
> I need to swap the values in column B with the values in column C
> and vice versa.
This should work with MS SQL assuming columns b and c are
the same data type:
UPDATE
Are you talking about a one time query to change the values stored in the
DB?
UPDATE tableName
SET
tableName.B = tableName.C,
tableName.C = tableName.B
WHERE tableName.A = tableName.A
Adam.
> -Original Message-
> From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
> Se
yeap actually simplier...
INSERT t1
SELECT a as a , b as c ,c as b
FROM t1
TEST b4 you do it
WG
-Original Message-
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: 03 March 2003 14:44
To: CF-Talk
Subject: RE: OT SQL Question
SQL 7
does this still apply?
-Original
SQL 7
does this still apply?
-Original Message-
From: webguy [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2003 9:42 AM
To: CF-Talk
Subject: RE: OT SQL Question
SQL server ? use a format like this..
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING&#
SQL server ? use a format like this..
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.
Thanks Everyone! I appreciate the help!
Thanks,
Tony
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 7:26 PM
To: CF-Talk
Subject: Re: OT: SQL Question
Tony Carcieri wrote:
> Hi all,
>
> Here's what I want t
Tony Carcieri wrote:
> Hi all,
>
> Here's what I want to do:
> UPDATE tablename
> SET column = 0
> WHERE ID = ???
>
> I want to specify a range of numbers (like 100-200) and increment it by 2.
> So, 100, 102,104etc would only be updated and the rest wouldn't.
WHERE
ID BETWEEN 100 AN
o:[EMAIL PROTECTED]]
Sent: 29 July 2002 21:27
To: CF-Talk
Subject: Re: OT: SQL question
Great. You did not provide your database or setup. I can only assume you
are working on a mainframe hitting DB2.
On Mon, 29 Jul 2002, Phillip B wrote:
> I need to do this and don't know where to start
Great. You did not provide your database or setup. I can only assume you
are working on a mainframe hitting DB2.
On Mon, 29 Jul 2002, Phillip B wrote:
> I need to do this and don't know where to start.
>
> Compare part of a part number in one table to another table. The part numbers look
>like
Well DOH!! Thanks to all of you who pointed out to me the blindingly
obvious - all I had to do was join the tables. Jeez, I knew that .. I had
just momentarily forgotten is all. Bob's answer was one of many who pointed
out what I should obviously have known and recalled.
The only extra piece
On 1/16/01, Gieseman, Athelene penned:
>
> INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date,
>Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount)
> values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#',
>'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Not
That was it! Thank you!
-Original Message-
From: Bud [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 16, 2001 4:18 PM
To: CF-Talk
Subject: Re: OT: SQL Question
On 1/16/01, Gieseman, Athelene penned:
>
> INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date,
&
On 1/16/01, Gieseman, Athelene penned:
>
> INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date,
>Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount)
> values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#',
>'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Not
This hurts to look at. Is that the entire table? Or are there
additional fields that make those rows unique? If it's the entire table,
why do you have identical rows, and why do you want to retain them? If
there's more to the table, extend your 'where' clause to identify the
unique row that yo
Oh dear. Non-unique rows. Hmmm...
Unless its possible to do this with cursors (I know nothing about them), I'd say
you'd have to delete, then re-insert the data, or better yet add another column
to make it unique... ;)
David Cummins
Gavin Myers wrote:
>
> here's what i'm doing
>
> delete from
Which of Fred's OrderNums do you want? If you don't care, then use
SELECT DISTINCT Name
FROM table
If you want the Minimum, try
SELECT Name, MIN (OrderNum)
FROM table
GROUP BY Name
> -Original Message-
> From: PC [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 03, 2000 10:45 PM
>
SELECT DISTINCT Name, OrderNum
FROM tablename
___
Pete Freitag
CFDEV.COM
Cold Fusion Developer Resources
http://www.cfdev.com/
-Original Message-
From: PC [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 03, 2000 4:45 PM
To: [EMAIL PROTECTED]
Su
55 matches
Mail list logo
