On 4/13/2013 1:55 AM, Rick Root wrote:
> It occurs to me that even If I put the data in correctly, I still have to
> "deal" with it because I can't really output a utf-16 character to a web
> page (or can I?).. I dunno maybe it just works.
no, it will show up fine (your db driver & cf will see t
Thanks Paul.
It occurs to me that even If I put the data in correctly, I still have to
"deal" with it because I can't really output a utf-16 character to a web
page (or can I?).. I dunno maybe it just works.
time to play...
~|
On 4/11/2013 11:53 PM, Rick Root wrote:
> No, it doesn't. Not really.
>
> http://msdn.microsoft.com/en-us/library/bb330962(v=sql.90).aspx
actually that page and a decade of my experience says it does. UTF-8 will get
transformed (its designed for that) to UCS2 by the db driver. for all practica
I'm gonna try to get the SAP people to send me a UTF-16 file instead
(essentially the same as the link except I wouldn't have to convert it :) )
On Thu, Apr 11, 2013 at 1:03 PM, Russ Michaels wrote:
>
> you could try this work around.
>
>
> http://stackoverflow.com/questions/5498033/how-to-wri
you could try this work around.
http://stackoverflow.com/questions/5498033/how-to-write-utf-8-characters-using-bulk-insert-in-sql-server
On Thu, Apr 11, 2013 at 5:53 PM, Rick Root wrote:
>
> On Tue, Mar 26, 2013 at 11:10 PM, Paul Hastings >wrote:
>
> >
> > > SQL Server 2005 does not support
On Tue, Mar 26, 2013 at 11:10 PM, Paul Hastings wrote:
>
> > SQL Server 2005 does not support UTF-8 apparently.
>
> sure it does.
>
No, it doesn't. Not really.
http://msdn.microsoft.com/en-us/library/bb330962(v=sql.90).aspx
I'm loading this data from UTF-8 encoded files coming from an SAP sys
On 3/27/2013 3:08 AM, Rick Root wrote:
> AMEX � ADR Box
>
> The em dash seems to come through in my text file is 3 characters when I
> view it in notepad++
that's because your data is garbaged or its encoding is either missing or
misidentified or i guess notepad++ doesn't understand unicode (n
Sorry, the em dash is 915-199-244, not 915-244-71.
a unicode em dash is unicode 2015 .. I'm not sure how these numbers relate
though
On Tue, Mar 26, 2013 at 4:08 PM, Rick Root wrote:
>
> Hi all,
>
> I am getting some data feeds from our SAP system (god help me). Some of
> the data contai
Hi all,
I am getting some data feeds from our SAP system (god help me). Some of
the data contains unicode characters apparently like em dashes and such.
For example, take the following string:
AMEX ADR Box
The em dash seems to come through in my text file is 3 characters when I
view it in n
Russ,
You did, of course, state why, but it did not click until you just reworded
it. Thank you! It makes perfect sense now.
(And yes, I had already changed to removing noise words from the search
string.)
~|
Order the Adobe
Original Message-
From: Matt Quackenbush [mailto:quackfu...@gmail.com]
Sent: 23 November 2010 22:07
To: cf-talk
Subject: Re: (ot) SQL Server Full-Text Search CONTAINS()
@ Russ-
Thanks for the reply. I've read that page so many times that it's all just
gibberish right now. My b
@ Russ-
Thanks for the reply. I've read that page so many times that it's all just
gibberish right now. My brain is mush. LOL
Suffice it to say, as you stated, when encountered in a FTS search string,
noise/stop words block any results from being delivered.
@ Ian-
I had not tried that prior
Just an off-the-wall idea.
Did you try a different order to the words? I.E. chemical NEAR romance
AND my
I was speculating what might happen of the 'noise' word came later in
the list.
~|
Order the Adobe Coldfusion Anthol
I now recall having this issue myself many years ago, and the reason it
doesn't work is because the noise words (now called stop words) are not
actually indexed, rather than being stripped from the search which is what
you assumed. On SQL 2000 you couldn't even use the stop words period or you
get
Carl,
Thanks for that link. I appreciate it. :-)
I guess I am simply totally misunderstanding noise/stop words in SQL
Server. My understanding was that they were ignored in a query. But the
behavior I am seeing indicates that the entire search string is ignored if
such a word is encountered.
Matt,
I googled on "sql server noise words" and found this link:
http://arcanecode.com/2008/05/29/creating-and-customizing-noise-words-in-sql-server-2005-full-text-search/
I followed the directions and found the noise lookup file being used by SQL
Server 2005 on my computer, and confirmed that
Thanks for the tip on using just a single NEAR keyword. In this particular
example (my chemical romance), it still results in no records being
returned. The only thing I can figure is that 'my' is a noise word, and all
searching is ignored once a noise word is hit.
For the time being, I've mere
t [mailto:mgr...@modus.bz]
Sent: Tuesday, November 23, 2010 2:07 PM
To: cf-talk
Subject: Re: (ot) SQL Server Full-Text Search CONTAINS()
I'm not overly familiar with NEAR but most of the examples I've seen where
NEAR is strung together show it without the quotes.
CONTAINS (EventName,&#x
@ Michael-
Unfortunately, you are correct: That's no help. Same result without the
quotes. :-(
I know that "noise words" are ignored, but what appears to be happening is
that it is ignoring the entire thing, rather than just the (possible) noise
word "my".
~~~
I'm not overly familiar with NEAR but most of the examples I've seen where
NEAR is strung together show it without the quotes.
CONTAINS (EventName,'my NEAR chemical NEAR romance')
That's probably no help, but I thought I'd share.
On Tue, Nov 23, 2010 at 1:00 PM, Matt Quackenbush wrote:
>
> S
Ayep. The band name is "My Chemical Romance". I'd say that definitely
qualifies as NEAR. :-)
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archi
Are there any results that actually have those 3 words near each other ?
Russ
-Original Message-
From: Matt Quackenbush [mailto:quackfu...@gmail.com]
Sent: 23 November 2010 18:01
To: cf-talk
Subject: (ot) SQL Server Full-Text Search CONTAINS()
SQL Server 2005
For the life of me, I
SQL Server 2005
For the life of me, I cannot figure out why the hell this...
WHERE
CONTAINS (EventName,' "my" NEAR "chemical" NEAR "romance" ')
...returns 0 results, yet this...
WHERE
CONTAINS (EventName,' "chemical" NEAR "romance" ')
...returns the correct records.
Why does "m
Figured it out. I was missing a join. Should have been:
SELECT DISTINCT phases.phase AS phaseLabel,
narratives.title,
narratives.id,
narratives.content,
phases.id AS phaseId
FROM( ( nsftool.phases2company phases2company
I am having a SQL problem that I'm guessing amounts to me just being brain
dead today but I'd really appreciate any help with this. Here is the query:
SELECT phases.phase AS phaseLabel,
narratives.title,
narratives.id,
narratives.content,
phases.id AS phaseId
FROM
I am hoping to tap into the knowledge of any ColdFusion developers who
use SQL Server 2005.
I am creating a table to load a large amount of data from text files.
One column contains a date string and one column contains a time
string. I am hoping to load these into string fields in the datab
Bangs head on desk... Needs more coffee...
Don't know why I tried to overcomplicate things, but that seems to work.
Thanks!
CV
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Wednesday, January 13, 2010 11:08 AM
To: cf-talk
Subject: Re: (ot) SQL Guru Needed
How about this:
Select min(optprice) as minprice, max(optprice) as maxprice
fromoptions
where optID in (select optID
fromproductOptions
where productID = )
On Wed, Jan 13, 2010 at 11:02 AM, Che Vilnonis wrote:
>
> Hello. Any SQL Guru up for a challenge? I've inherited a wacky datab
What about a basic join?
SELECT MIN(optPrice) AS minPrice, MAX(optPrice) AS MaxPrice
FROMproductOptions po
INNER JOIN options o ON o.optID = po.optID
WHERE productID =
~|
Want to reach the ColdFusion
Hello. Any SQL Guru up for a challenge? I've inherited a wacky database that
I need to pull info from. I'm trying to pull a range of prices for a
specific product. I'd like to simply the two queries below (which work),
possibly using sub-queries or some other SQL trick. Any ideas?
select optID
It looks like the reason for the original rewrite was due to subquerying
the same table twice. Nothing wrong with that, but the wizard decides to
ensure that every reference is unique, thus the aliasing of the second
reference. You could get around that by adding your own table aliases in
th
> I would recommended creating the view from a query window,
> and not the
> design view that SQL Management studio uses
+1 . Traditionally design views and wizards do unspeakable things to your
nicely formatted sql.
-Leigh
AM
To: cf-talk
Subject: Re: (ot) SQL query aliases in SQL Management Studio
Hi Jason,
I would recommended creating the view from a query window, and not the
design view that SQL Management studio uses
Simply add a 'Create View dbo. AS' before the statement that works and
run it
Hi Jason,
I would recommended creating the view from a query window, and not the
design view that SQL Management studio uses
Simply add a 'Create View dbo. AS' before the statement that works and
run it in the query window
Hope this helps
-Dave
On Tue, Dec 22, 2009 at 11:34 AM, Jason Durha
My apologies for posting an OT message to a ColdFusion list. RDMS are so
closely tied to our day-to-day job in writing CF, that perhaps the list will be
tolerant of such a question.
I'm writing a View for our CRM package to use. The SQL works as I've written
it, but Management Studio keeps a
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
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
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
FYI,
a) Project deadline is days away. One should probably not to change horse
while crossing a river (of course another could argue "should you really need
to cross the river?")
b) The randomly reported error may not accurately reflect sql server 2005
express sp3 installation success or failu
Out of curiosity, after all the problems you have had with MSSQL Express
and all the alternatives that have been offered. Are you going to continue
to use MSSQL Express? Or will you try using one of the alternatives?
Best Regards.
Emmit
On Wed, Aug 12, 2009 at 6:27 PM, Don L wrote:
>
> > On We
> On Wed, Aug 12, 2009 at 5:19 AM, Don L wrote:
> >> Mysql, sql lite, h2, derby = no install
> > sql lite is eliminated for it's not a dbms but a c library for a dbm.
> of the remaining 3, which one is most compatiable with ms sql server
> 2005 syntax and feature-wise?
>
> PostgreSQL.
>
> Su
>Looks like it's still Vista SP1, Home Premium Edition.
>
>No clue what version of the Installer is in place. There are no entries
>in those keys that are specific to the installer itself, at least not on
>my OS.
Good to know, thanks. Those two registry branches are pretty standard for XP
pr
Looks like it's still Vista SP1, Home Premium Edition.
No clue what version of the Installer is in place. There are no entries
in those keys that are specific to the installer itself, at least not on
my OS.
~|
Want to reach t
> Actually, I run SQL 2005 Express on my Vista box at home for dev work,
> and it's actually been fine. SQL Express has its own problems (what
> do you mean I can't move data with it??), but that's not a Vista issue.
Could you look up Windows Installer version from registry on your Vista box f
On Wed, Aug 12, 2009 at 5:19 AM, Don L wrote:
>> Mysql, sql lite, h2, derby = no install
> sql lite is eliminated for it's not a dbms but a c library for a dbm. of the
> remaining 3, which one is most compatiable with ms sql server 2005 syntax and
> feature-wise?
PostgreSQL.
Supports silent
Actually, I run SQL 2005 Express on my Vista box at home for dev work, and it's
actually been fine. SQL Express has its own problems (what do you mean I can't
move data with it??), but that's not a Vista issue.
~|
Want to rea
sql lite is eliminated for it's not a dbms but a c library for a dbm. of the
remaining 3, which one is most compatiable with ms sql server 2005 syntax and
feature-wise? do they all support quiet installation? do h2 and derby support
dll as well? how about creating datasource connection to rail
Mysql, sql lite, h2, derby = no install
All of which have rich SQL dialects and are very capable.
Problem solved!
G!
On Tue, Aug 11, 2009 at 9:47 PM, Don L wrote:
>
> I'd like to hear people's experience on this, my users/customers reported
> tons of problem with it while generally positive
I'd like to hear people's experience on this, my users/customers reported tons
of problem with it while generally positive on XP. I don't have vista box
myself, can't test it on my own. thks.
~|
Want to reach the ColdFusion
On Wed, Mar 11, 2009 at 3:01 AM, Rick Root wrote:
> The server has 4x146gb scsi drives set up in 2 RAID-1 mirrored sets.
>
> We ended up with a 20 gig C drive and a 120gb D: drive on one mirrored
> set, and 70 gig E: and F: drives on the other mirrored set. Currently
> we've got our SQL Server da
Dear list,
We had an engagement with the SeeFusion folks a few months ago (6?) in
which they made some recommendations to us regarding a new SQL Server
box. I'm not sure if our hosting company did what they recommended (I
was not involved in that part of the engagement) but here's what we've
got
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
-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
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
&
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
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
>
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
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
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
Have you checked the Long Text Buffer setting, under Advanced Settings for the
Datasource in CF Administrator? Maybe it's at 32000 or something?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
Are you using cfstoredproc or cfquery? If cfquery, can you reply with body
of query?
On Wed, Feb 11, 2009 at 9:48 AM, Les Mizzell wrote:
>
> SQL Server 2000 (8.0.x)
> Database field type "ntext"
> Using FCKEditor as a rich text editor for this field
>
> There's no restriction on size in the in
check our Datasource settings
make sure enable large text retrieval is ON
On Wed, Feb 11, 2009 at 10:48 AM, Les Mizzell wrote:
>
> SQL Server 2000 (8.0.x)
> Database field type "ntext"
> Using FCKEditor as a rich text editor for this field
>
> There's no restriction on size in the insert query.
ary 2009 15:48
> To: cf-talk
> Subject: (ot) SQL 2000 + FCKEdit truncation problem
>
>
> SQL Server 2000 (8.0.x)
> Database field type "ntext"
> Using FCKEditor as a rich text editor for this field
>
> There's no restriction on size in the insert query.
>
On Wed, Feb 11, 2009 at 4:48 PM, Les Mizzell wrote:
> SQL Server 2000 (8.0.x)
> Database field type "ntext"
> Can anyone come up with a reason why all inserts are truncated after
> approx 31,000 (give or take) characters?
Did you enable CLOB support in the advanced properties of the DSN definiti
SQL Server 2000 (8.0.x)
Database field type "ntext"
Using FCKEditor as a rich text editor for this field
There's no restriction on size in the insert query.
There's no restriction on the amount of stuff that can be typed/pasted
into the field.
Can anyone come up with a reason why all inserts ar
[mailto:[EMAIL PROTECTED]
Sent: Thursday, September 11, 2008 4:34 PM
To: CF-Talk
Subject: Re: (ot) SQL Join With LIKE in Join Criteria
Hmm so long as the data in the 2nd table is consistent, you are
safe.
If one day, the 2nd table has data like Bad_Data_2008FA_bad, then your
solution wi
Hmm so long as the data in the 2nd table is consistent, you are safe.
If one day, the 2nd table has data like Bad_Data_2008FA_bad, then your
solution will break.
This solution, I think, at its best, is temporary. A more permenant
solution will be to have a REF table where the value of table 1
Still, it's
pretty darn quick.
Thanks
-Original Message-
From: Jeff Becker [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 11, 2008 4:07 PM
To: CF-Talk
Subject: Re: (ot) SQL Join With LIKE in Join Criteria
Very interesting Michael. A good solution based on the environment your
wo
Very interesting Michael. A good solution based on the environment your working
with. I'm no DBA, but I do have a feeling this is an example where proper
normalization would help out significantly. In terms of performance, I have no
idea if you'll have a problem, but I believe anytime a LIKE '
I am joining two table based on a partial match of data. The data is
for our school's terms/semesters and the courses for those terms. My
query works fine, as far as I can tell. I just want to make sure
someone else doesn't see anything wrong with my solution.
One table has values such as:
2
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
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
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
Just to follow up: From what I can tell you cannot use special characters with
the find + replace option in the Studio Express _editor_. I ended up using
another editor.
Thanks for the response Sonny. I know my original question was about as clear
as .. mud ;-)
~
>You're doing a code search? I assumed you were trying to format data being
>returned by a query.
Sort of. I am working with some ad-hoc sql scripts. Sorry, if I was not clear.
I know this is OT. I posted in the hopes that someone else using Express Studio
might know the magical character(s).
If you're wanting to change the formatting of existing data in a column, it
could be done this way:
UPDATE my_table
SET list_column = replace(list_column, '|', CHAR(13) + CHAR(10))
WHERE list_column LIKE '%|%'
On Fri, Jul 18, 2008 at 4:22 PM, Sonny Savage <[EMAIL PROTECTED]> wrote:
> You're doin
You're doing a code search? I assumed you were trying to format data being
returned by a query.
On Fri, Jul 18, 2008 at 4:06 PM, C S <[EMAIL PROTECTED]> wrote:
> >CHAR(13) + CHAR(10)
>
> Thanks, but I mean using Studio Express's find + replace option. It is such
> a simple thing, but it has me s
>CHAR(13) + CHAR(10)
Thanks, but I mean using Studio Express's find + replace option. It is such a
simple thing, but it has me stumped. I have tried every combination I can think
of. No joy.
~|
Adobe® ColdFusion® 8 software 8
CHAR(13) + CHAR(10)
On Fri, Jul 18, 2008 at 2:15 PM, C S <[EMAIL PROTECTED]> wrote:
> OT: SQL 2005 Studio Express - find and replace with newline character
>
> Does anyone know how to find and a character and replace it _with_ a
> newline using SQL Server 2005 Studio Expres
OT: SQL 2005 Studio Express - find and replace with newline character
Does anyone know how to find and a character and replace it _with_ a newline
using SQL Server 2005 Studio Express? For example, I would like to replace all
"|"'s in the following string
one|two|thre
All,
I have a unique problem. In SQL 2000 there is a way to convert a
function to being a system function. I.E. instead of
master.dbo.listToTable() you could use fn_listToTable().
The same conversion of a function doesn't work in SQL 2005. This is
causing a problem because we are upgrading f
I'll give that a try and see what happens.
-Original Message-
From: Gerald Guido [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2008 9:09 AM
To: CF-Talk
Subject: Re: OT: SQL Server Accessing Excel file.
Is there any way you can copy the file and use the copy? That was my
Is there any way you can copy the file and use the copy? That was my first
thought.
On Mon, Mar 10, 2008 at 8:29 AM, DURETTE, STEVEN J (ATTASIAIT) <
[EMAIL PROTECTED]> wrote:
> Hi all,
>
> Sorry for the off topic, but this has been a real pain in my behind. We
> have a process were our SQL serve
Hi all,
Sorry for the off topic, but this has been a real pain in my behind. We
have a process were our SQL server reads in data from an Excel file.
>From there the data is used in various CF pages.
The problem is that the file that we import data from is opened by users
to make changes before
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
1 - 100 of 964 matches
Mail list logo
