473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

specify a non-linear order by

How can I order the results of my query in non-linear fasion. I have a
field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.

Jul 23 '05 #1
4 1661
You can do it like this:

....ORDER BY CASE fieldname WHEN 'US' THEN 1 WHEN '144A' THEN 2 WHEN
'Reg S' THEN 3 ELSE 4 END

Alpine7 wrote:
How can I order the results of my query in non-linear fasion. I have a field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.


Jul 23 '05 #2
Thanks that is exactly what I was looking for works great.

Jul 23 '05 #3
SELECT ..
CHAR_INDEX ('US, 144A, Reg S.') AS sort_col
FROM Foobar
ORDER BY sort_col;

Jul 23 '05 #4
I would point out that, whenever you are repeating a text value in a column of
a table, you should probably be using a surrogate key to a lookup table
instead. Your new requirement for a specific sort order for the codes drives
this point home. If you create a lookup table for your codes, you can add a
sort order attribute to the codes table and not be forced to choose between
adding another column to your main table or using SQL that will be invalid the
next time a code is added.

Example:

paper_type
----------
(pk) paper_type_id int identity
(u) paper_type_code varchar(10)
order_sequence int

document
--------
(pk) document_id
(fk) paper_type_id
document_title

To query from these tables in the document type sort order, ...

SELECT
document.document_title,
paper_type.paper_type_code
FROM
document
INNER JOIN paper_type
ON document.paper_type_id = paper_type.paper_type_id
ORDER BY
paper_type.order_sequence
document.document_title

On 9 Dec 2004 07:53:16 -0800, "Alpine7" <da****@datashock.com> wrote:
How can I order the results of my query in non-linear fasion. I have a
field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.


Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Lasse Edsvik | last post by:
Hello I have a mailserver that requires windows authentication for sending email. could you guys show me an example of how to specify user and password in CDO/CDONTS? i cant configure it to...
12
by: Jim Cochrane | last post by:
I just google-searched this group and could not find any references to this. I'm trying to figure out how to specify a three-part header with html. For example, left part ...
6
by: Tony Marston | last post by:
The code <a href="..." target="_blank">...</a> will not validate as XHTML STRICT because of the 'target' tag, so how do I achieve the same result by moving it to a CSS file? I cannot find anything...
1
by: Andrew Biagioni | last post by:
Hi all! I'm trying to write a general-purpose trigger that determines what fields have changed during an UPDATE, but I'm running into a problem. I'm trying to dynamically select the value from...
2
by: Tony | last post by:
Yes, I need to specify a font type so that the characters will be evenly spaced when I write to a tab delimited text file. So how does one specify a font type to write/print and which font is...
0
by: Rajiv Das | last post by:
..Net 2.0 --------- Assume I am on a non-domain network. The proxy asks me to authenticate whenever I want to access the net. I have an app, through which I wish to access web sites using...
0
by: sylvain | last post by:
I create a deployment kit with VS and I want to specify a Web Site different then the Default Web Site. There are two Web Site on our Web Server and they are using the same Port (two different...
15
by: jacob navia | last post by:
Problem You want to ensure that a pointer argument to a function is non-null. Solution int fn(double data); This means that the array (that is passed as a pointer)
3
by: =?Utf-8?B?Q0QuU21hbGxleQ==?= | last post by:
Is there a command line switch available for the EventViewer which will allow me to specify which directory to view the available logs from? I will have multiple workstations writing to a...
2
by: zz12 | last post by:
Hello. Would anyone know if there is an easy and clean way of presenting maybe a time control field in html in an .asp page that allows a user to specify a certain time of the day? Currently...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.