Re: SQL Question, incrementing values

2012-12-13 Thread Scott Stewart
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

Re: SQL Question, incrementing values

2012-12-13 Thread Scott Stewart
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 =

Re: SQL Question, incrementing values

2012-12-13 Thread John M Bliss
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

SQL Question, incrementing values

2012-12-13 Thread Scott Stewart
Hey all I have a couple of questions with a query that I'm trying to put together What I need as part of this query is a running count of applications that each value of cc_type_id (there's five). I'm trying to do it in the CASE statement below, however if I try to use AS something in order to

Re: (ot) SQL Question - flattening data

2009-11-15 Thread Dominic Watson
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 >

Re: (ot) SQL Question - flattening data

2009-11-15 Thread 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 rick,TR rick,HS joe,AL joe,FU Bob,FM And then use cfoutput with query and group (a very rough output here):

RE: (ot) SQL Question - flattening data

2009-11-14 Thread Dave Phelan
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

Re: (ot) SQL Question - flattening data

2009-11-13 Thread Rick Root
>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

RE: (ot) SQL Question - flattening data

2009-11-13 Thread DURETTE, STEVEN J (ATTASIAIT)
] 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

RE: (ot) SQL Question - flattening data

2009-11-13 Thread Dave Phelan
X Bob X Crosstab queries can be a little hairy to build. IMHO, go with the cursors. -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: Friday, November 13, 2009 10:41 AM To: cf-talk Subject: (ot) SQL Question - flattening

(ot) SQL Question - flattening data

2009-11-13 Thread Rick Root
I'm trying to flatten out some data using only SQL we currently have a mainframe job that produces a datafeed for me uses cobol to do the work of looping through all the entities and putting up to 5 record types in 5 "record type" fields in the output file. I'm trying to figure out a way

Re: simple SQL Question

2009-08-22 Thread Mike Chabot
ug 21, 2009 at 8:09 PM, Discover Antartica wrote: > > *= is actually used at my work place.  Thanks for the answer. > > > > > > From: Adrian Lynch > To: cf-talk > Sent: Friday, August 21, 2009 5:59:17 PM > Subject: RE: simple SQL Qu

Re: simple SQL Question

2009-08-21 Thread Discover Antartica
*= is actually used at my work place.  Thanks for the answer. From: Adrian Lynch To: cf-talk Sent: Friday, August 21, 2009 5:59:17 PM Subject: RE: simple SQL Question That is a left outer join. It's mixing new and old styles of joining tables. Not su

RE: simple SQL Question

2009-08-21 Thread Adrian Lynch
e question? Adrian > -Original Message- > From: Discover Antartica [mailto:discoverantart...@yahoo.com] > Sent: 22 August 2009 00:50 > To: cf-talk > Subject: simple SQL Question > > > what does the *= mean in a query. For example: > > select a.id, b.na

simple SQL Question

2009-08-21 Thread Discover Antartica
what does the *= mean in a query. For example:   select a.id, b.name  from a inner join b    on a.id = b.id where a.id *= b.id   Thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
02-25 7:00' >> union all select 2, 'McSteamy', '2009-02-25 7:45' >> >> insert into @first_appointment_per_day >> (patient_id, appointment_date) >> (select patient_id, min(appointment_date) >> from @appointment >> group by patient_id, conv

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Dan Baughman
-25 7:00' > > union all select 2, 'McSteamy', '2009-02-25 7:45' > > > > insert into @first_appointment_per_day > > (patient_id, appointment_date) > > (select patient_id, min(appointment_date) > > from @appointment > > group by patient

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
t patient_id, min(appointment_date) > from @appointment > group by patient_id, convert(varchar(10), appointment_date, 101)) > > select app.patient_id, app.appointment_date, app.doctor > from @first_appointment_per_day tmp1 > inner join @appointment app on tmp1.patient_id = app.patient_id &

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
This is the sort of approach I was trying to think of, couldn't remember derived tables. This query only grabs the earliest appointment for each patient though not the earliest for every day. But it is a good starting point, I'll try to massage a group by date in there and see what I can come up w

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Brian Peddle
Maybe Select top 1 * from appointment where appointment between (date/time and date/time) order by appointment_date Ben Conner wrote: > Hi Judah, > > I suppose there's more than one way to do this, but this should work... > > SELECT a.* > FROM appointment a INNER JOIN >

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Ben Conner
Hi Judah, I suppose there's more than one way to do this, but this should work... SELECT a.* FROM appointment a INNER JOIN (SELECT patient_id, MIN(appointment_date) AS appointment_date FROM appointment

RE: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread brad
ointment_date) from @appointment group by patient_id, convert(varchar(10), appointment_date, 101)) select app.patient_id, app.appointment_date, app.doctor from @first_appointment_per_day tmp1 inner join @appointment app on tmp1.patient_id = app.patient_id and tmp1.appointment_date = app.appointment_date ~Brad

Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
I'm ill and having difficulty wrapping my head around an issue I know I've done before, so I'm hoping that someone can help me out. I have a table that lists patient's appointments. Appointment id patient_id doctor appointment_date (datetime) A patient can have 0...n appointments o

Re: SQL question

2009-02-17 Thread Mike Soultanian
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

Re: SQL question

2009-02-16 Thread Brian Kotek
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

Re: SQL question

2009-02-16 Thread Dave Watts
> 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

Re: SQL question

2009-02-16 Thread Mike Kear
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

SQL question

2009-02-16 Thread Mike Soultanian
I was curious if anyone knows how you describe the following SQL functionality: SELECT 'mike' as name returns a single column named "name" with a single row containing "mike" I also know you can do stuff like: SELECT 1 Which returns a column named "1" with a single row containing "1", or: S

Re: (Probably) Simple SQL Question?

2008-11-25 Thread Michael Casey
A simple 'group by' clause ought to get you there. With your first example, try this: select id, max(name), max(date_due), max(date_modified) from yourQry group by id order by date_modified desc With your second example, with the composite key, try concatenating t

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Seb Duggan
If you're not using SQL 2005, the following (slightly more complex) query should simulate the RANK and PARTITION: SELECT id, name, date_due, date_modified FROM ( SELECT id, name, date_due, date_modified, (SELECT COUNT(id) FROM table T2 WHERE T1.id = T2.id AND T2.date_modified >= T1.date_modified

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Sung Woo
> What database are you using? We're still using SQL2000, so 2005 commands won't help me here...thanks... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.double

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Sung Woo
Thanks, Gerald. This is close, except there is a bit of a problem. I need to group this not by just one field but three fields, so the dataset actually looks like this (fields 1 - 3 make up the unique key): field 1 field 2 field 3 date_due date_modified 1 2 3 1/1/2009 11/18

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Gerald Guido
Opps l messed up swap out "name" for ID in the WHERE WHERE name IN ('SELECT DISTINCT name SELECT TOP(date_due) as TOP_date_due, name, date_modified FROM Table WHERE name IN ('SELECT DISTINCT name FROM Table ') GROUP BY name, date_modified, date_due ORDER BY da

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Alan Rother
SELECT id, name, date_due, date_modified FROM SomeTable WHERE On Tue, Nov 18, 2008 at 3:38 PM, Sung Woo <[EMAIL PROTECTED]> wrote: > Actually, your suggestion wouldn't work for the first set, either, as it > would bring up 2 records for Smith and none for Woo. > >

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Gerald Guido
I don't remember the syntax for the Sub query but something like this may work: SELECT TOP(date_due) as TOP_date_due, name, date_modified FROM Table WHERE ID IN ('SELECT DISTINCT name FROM Table ') GROUP BY name, date_modified, date_due ORDER BY date_modified ,

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Seb Duggan
What database are you using? In MS SQL 2005 you can use the RANK and PARTITION keywords: SELECT id, name, date_due, date_modified FROM ( SELECT id, name, date_due, date_modified, RANK() OVER (PARTITION BY id ORDER BY date_modified DESC) AS userRank FROM table ) AS t2 WHERE t2.userRank <= 2 ORDER

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Sung Woo
Actually, your suggestion wouldn't work for the first set, either, as it would bring up 2 records for Smith and none for Woo. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Tr

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Sung Woo
Hi Rob, That would work in this instance, but I need something a little more dynamic. What if the dataset looked like this? id name date_due date_modified 1 Woo 1/1/2009 11/18/2008 4:55PM 1 Woo 2/1/2009 11/18/2008 5:21PM 1 Woo 3/1/2009 11/18/2008 5:30PM 2 Smith 1/1/2009 11/18/200

Re: (Probably) Simple SQL Question?

2008-11-18 Thread Rob Parkhill
select top 2 * from tables order by date asc should get you there. Rob On Tue, Nov 18, 2008 at 5:00 PM, Sung Woo <[EMAIL PROTECTED]> wrote: > There's probably a really simple answer to this. Here's the sample > dataset: > > id namedate_duedate_modified > 1 Woo 1/1/200

(Probably) Simple SQL Question?

2008-11-18 Thread Sung Woo
There's probably a really simple answer to this. Here's the sample dataset: id namedate_duedate_modified 1 Woo 1/1/200911/18/2008 4:55PM 1 Woo 2/1/200911/18/2008 4:57PM 1 Woo 3/1/200911/18/2008 4:59PM 2 Smith 1/1/2009

RE: odd sql question

2008-10-29 Thread Adrian Lynch
database of ColdFusion errors at http://cferror.org/ -Original Message- From: Scott Stewart Sent: 29 October 2008 16:58 To: cf-talk Subject: odd sql question i have a table with a field (study_category.category_date) that is a comma delimited list (2004,2005,2006,2007) stored as a NVARC

Re: odd sql question

2008-10-29 Thread Ian Skinner
Scott Stewart wrote: > i have a table with a field (study_category.category_date) that is a > comma delimited list (2004,2005,2006,2007) stored as a NVARCHAR > > I need to see if a passed value is in the list: > > WHERE (#arguments.cat_date# in (study_category.category_date)) > > arguments.cat_da

Re: odd sql question

2008-10-29 Thread Jason Fisher
Pretty sure that's not going to work for you ... believe the argument on the left of the IN has to be a column, not a variable. In any case, your best bet is to have the category_date properly normalized out into a separate table: FROM study_category INNER JOIN study_category_date ON study_cate

odd sql question

2008-10-29 Thread Scott Stewart
i have a table with a field (study_category.category_date) that is a comma delimited list (2004,2005,2006,2007) stored as a NVARCHAR I need to see if a passed value is in the list: WHERE (#arguments.cat_date# in (study_category.category_date)) arguments.cat_date is a four digit number represen

RE: (ot) SQL question...

2008-08-19 Thread Che Vilnonis
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

RE: (ot) SQL question...

2008-08-19 Thread Mark Kruger
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

RE: (ot) SQL question...

2008-08-19 Thread Dave Phillips
BY source ORDER BY source DESC Sincerely, Dave Phillips http://www.dave-phillips.com -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2008 9:11 AM To: CF-Talk Subject: (ot) SQL question... Hello all. I'm looking to output the total numb

(ot) SQL question...

2008-08-19 Thread Che Vilnonis
Hello all. I'm looking to output the total number of entries, grouped by source from 3 tables. I'd like to modify the sql below so that each source shows up only once, yet tablulates the totals from all of the tables. Any ideas. Thanks, Che. --- select count(*) as total, source fr

RE: Transact SQL question has me stumped

2008-02-06 Thread Mark Kruger
8:47 AM To: CF-Talk Subject: RE: Transact SQL question has me stumped Dennis... Learn something new everyday. I guess I did not know there was bit operator in CF :) -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 1:11 PM To: CF-Talk Subjec

RE: Transact SQL question has me stumped

2008-02-06 Thread Mark Kruger
Dennis... Learn something new everyday. I guess I did not know there was bit operator in CF :) -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 1:11 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped Mark, I knew I would

RE: Transact SQL question has me stumped

2008-02-05 Thread Dennis Powers
Jeff, >> Ah, you changed the question :) You originally said AND, now it is OR! I knew I was phrasing it incorrectly mixing Boolean and linguistic "and" >> WHERE myColumn & myMask > 0 This is much too easy and I can't believe I overlooked so simple a basic Boolean solution. That sound you hear

Re: Transact SQL question has me stumped

2008-02-05 Thread Jeff Price
Ah, you changed the question :) You originally said AND, now it is OR! It doesn't change the problem much. Step 1: Create your mask Step 2: Zero out the bits we don't care about with Step 3: If we are left with anything, we have records that contain a flag. WHERE myColumn & myMask > 0 enjoy!

Re: Transact SQL question has me stumped

2008-02-05 Thread Jeff Price
doh! That should be 2^0 + 2^7 (silly me) > myMask = 2^1 + 2 ^8 ~| 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

RE: Transact SQL question has me stumped

2008-02-05 Thread Dennis Powers
Jeff, >> myMask = 2^1 + 2 ^8 >> SELECT mycolumns >> FROM mytable >> WHERE BigIntColumn & #myMask# = #myMask# This was exactly what I was doing - irrespective of the CAST to change data types - but it will only select records that have bit 1 AND Bit 8. What I need to do is to select records th

RE: Transact SQL question has me stumped

2008-02-05 Thread Brad Wood
Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 2:02 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped >> You need a bitwise operator. Bit and is & in MS SQL >> SELECT mycolumns >> FROM mytable >

Re: Transact SQL question has me stumped

2008-02-05 Thread Jeff Price
I'm not Guru, but this should do it. First you create a mask by turning on the bits you need. myMask = 2^1 + 2 ^8 Then, bitwise AND (&) with your column. The result needs to be equal to your mask to have all the specific bits turned on. NOTE: This assumes you don't care about the value in the

RE: Transact SQL question has me stumped

2008-02-05 Thread Dennis Powers
>> You need a bitwise operator. Bit and is & in MS SQL >> SELECT mycolumns >> FROM mytable >> WHERE bit_column & 128 = 128 This was essentially what I was doing but it does not work properly for matching multiple bits in the "bit_column", Example: matching a row that has Bit 1 and Bit 8 (129)

RE: Transact SQL question has me stumped

2008-02-05 Thread Brad Wood
Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 1:11 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped Mark, I knew I would most likely not explain it properly. The existing database has a column that contains values that ar

RE: Transact SQL question has me stumped

2008-02-05 Thread Dennis Powers
Mark, I knew I would most likely not explain it properly. The existing database has a column that contains values that are typed as a BigInt. I have a filter that gets constructed from Bit values for example: the filter = 129 which was constructed from bit 1 binary + bit 8 Binary 1 + 128. What

RE: Transact SQL question has me stumped

2008-02-05 Thread Mark Kruger
work :) -Mark -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 12:01 PM To: CF-Talk Subject: Transact SQL question has me stumped I am hoping an SQL guru can assist me with what I am sure is a stupid little oversight or misunderstanding on my p

Transact SQL question has me stumped

2008-02-05 Thread Dennis Powers
I am hoping an SQL guru can assist me with what I am sure is a stupid little oversight or misunderstanding on my part. I hope I can explain this. I need to do a bit evaluation against data in the database where the data is stored in a BigInit column. Within my code I construct a bit filter and nee

Re: OT: SQL Question -- Order by a column's value?

2008-01-26 Thread Brian Kotek
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
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

RE: SQL Question -- Order by a column's value?

2008-01-25 Thread Gaulin, Mark
Mark -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, January 25, 2008 1:41 PM To: CF-Talk Subject: OT: SQL Question -- Order by a column's value? Suppose I have a small set of data with a column named "Colleges". Is there a way to write an ORDER B

RE: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
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

RE: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Dawson, Michael
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Crow T. Robot
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Charlie Griefer
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

RE: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Todd
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Dominic Watson
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Todd
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

OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
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 ~

RE: SQL Question, get previous record

2008-01-15 Thread Dawson, Michael
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

Re: SQL Question, get previous record

2008-01-15 Thread Crow T. Robot
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,

RE: SQL Question, get previous record

2008-01-15 Thread Scott Stewart
: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#

RE: SQL Question, get previous record

2008-01-15 Thread Brad Wood
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#

RE: SQL Question, get previous record

2008-01-15 Thread Dawson, Michael
[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:21 PM To: CF-Talk Subject: SQL Question, get previous record I have a basic table It has a numeric primary key and a field that determines display order. The records are displayed based on the record order. However the record order may

SQL Question, get previous record

2008-01-15 Thread Scott Stewart
I have a basic table It has a numeric primary key and a field that determines display order. The records are displayed based on the record order. However the record order may not be continuous (IE: 1, 2, 5, 7, 10) The application calls for an up/down order change feature. What I need to b

RE: SOT sql question

2008-01-09 Thread Mark Fuqua
Nuf said. I'll do it the right way. -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 1:12 PM To: CF-Talk Subject: Re: SOT sql question Mark Fuqua wrote: > I know I could do a another table with JobFileId's and Access levels,

Re: SOT sql question

2008-01-09 Thread Ian Skinner
Mark Fuqua wrote: > I know I could do a another table with JobFileId's and Access levels, instead > of a > list of access levels, but it seems cleaner this way. > It is not. You have denormalized your data in such away that doing the type of select you want to do is very difficult. If you

RE: SOT sql question

2008-01-09 Thread Mark Fuqua
the current users access level which is stored in #session.PlumUserRoles# Any idea how I might do that? Thanks, Mark -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 12:11 PM To: CF-Talk Subject: Re: SOT sql question On Jan 9, 2008 8:

Re: SOT sql question

2008-01-09 Thread Marco Antonio C. Santos
Mark, I'll like to suggest you to use DataMgr. DataMgr is a great time saver tool and could be the answer to your needs. http://datamgr.riaforge.org/ Cheers Marco Antonio C. Santos On Jan 9, 2008 2:56 PM, Mark Fuqua <[EMAIL PROTECTED]> wrote: > I think I need to be using 'IN' within my WHERE c

Re: SOT sql question

2008-01-09 Thread Charlie Griefer
On Jan 9, 2008 8:56 AM, Mark Fuqua <[EMAIL PROTECTED]> wrote: > I think I need to be using 'IN' within my WHERE clause but I can't seem to > get it to work. I have a column with a comma delimited list. This is the > latest attempt and it craps out too. > > SELECT.. > ...WHERE JobFileJob = #sessio

RE: SOT sql question

2008-01-09 Thread Will Swain
IN works the other way round I think - say you have a list of values and you want to pull all records with any of those values: WHERE name IN (will,john,ray) -Original Message- From: Mark Fuqua [mailto:[EMAIL PROTECTED] Sent: 09 January 2008 16:57 To: CF-Talk Subject: SOT sql question

SOT sql question

2008-01-09 Thread Mark Fuqua
I think I need to be using 'IN' within my WHERE clause but I can't seem to get it to work. I have a column with a comma delimited list. This is the latest attempt and it craps out too. SELECT.. ...WHERE JobFileJob = #session.jobId# AND '#session.UserRole#' IN (JobFileAccessLevel) Job

Re: sql question: contains space' '

2008-01-03 Thread morchella
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

Re: sql question: contains space' '

2008-01-03 Thread morchella
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

RE: sql question: contains space' '

2008-01-03 Thread Mark Kruger
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

Re: sql question: contains space' '

2008-01-03 Thread Jim Wright
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®

Re: sql question: contains space' '

2008-01-03 Thread Paul Ihrig
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

Re: sql question: contains space' '

2008-01-03 Thread Ian Skinner
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

Re: sql question: contains space' '

2008-01-03 Thread Greg Morphis
'% ' 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

Re: sql question: contains space' '

2008-01-03 Thread morchella
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

Re: sql question: contains space' '

2008-01-03 Thread Greg Morphis
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

sql question: contains space' '

2008-01-03 Thread morchella
hey guys. this is a 2 parter. i have a table i need to fix. i have no real idea how to do this. the problem is the Contact filed. the first several thousand entries combine fnme & lname into this one field from a xls file. then maybe 20,000 entries only have a name with a space in the Contact fi

Re: SQL Question

2007-09-28 Thread Janet MacKay
> 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

Re: SQL Question

2007-09-28 Thread Janet MacKay
>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

  1   2   3   4   5   6   7   8   9   10   >