473,287 Members | 1,899 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,287 software developers and data experts.

SQL sort (Order by)

Hi all ,

I have sql statement below ,
SELECT
serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size,
country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,building and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,building,other gave me the result which i not expected.

For example below data before sort

Country City Building Other
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 83 Aber rd
Thailand Bangkok
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark

After sort

Country City Building Other
Thailand Bangkok 83 Aber rd
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok

Aug 10 '06 #1
6 8811

we********@gmail.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size,
country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,building and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,building,other gave me the result which i not expected.

For example below data before sort

Country City Building Other
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 83 Aber rd
Thailand Bangkok
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark

After sort

Country City Building Other
Thailand Bangkok 83 Aber rd
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok
There's probably a better solution but here's one way:

SELECT country,city,building
FROM tbl_chassis
ORDER BY
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(building
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;

Aug 10 '06 #2

we********@gmail.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size,
country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,building and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,building,other gave me the result which i not expected.

For example below data before sort

Country City Building Other
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 83 Aber rd
Thailand Bangkok
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark

After sort

Country City Building Other
Thailand Bangkok 83 Aber rd
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok
There's probably a better solution but here's one way:

SELECT country,city,building
FROM tbl_chassis
ORDER BY country,city,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(building
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;

Aug 10 '06 #3
Hi ,

Thanks for your help , it works.

I have some other issue regarding sort in MYSQL .

I have 2 table , tbl_chassis and tbl_card .
In tbl_chassis , the primary key is serial_no and In tbl_card the
primary key is serial_no.
The serial_no in tbl_chassis are link to serial_no in tbl_card.
For example ,

tbl_chassis
serial_no model
123 w123
890 ws890
345 cx1290

tbl_card
serial_no card_model
123 wx-123456
123 ab-123
123 wz-890
890 z-c123
890 cd-290
345 d123
345 ws-8901

I would like to order by model columns in tbl_chassis then order by
card_model in tbl_card . I have sql below but it not working at all :
Any idea how to sort columns in different table. Thanks

The data should display as shown below if sort by desc

123 ab-123
123 wz-890
123 wx-123456
345 d123
345 ws-8901
890 cd-290
890 z-c123

SELECT
DISTINCT(tbl_chassis.serial_no),tbl_chassis.host_n ame,tbl_chassis.chasis_sw_version,tbl_chassis.chas is_model,tbl_chassis.chasis_flash_size,tbl_chassis .chasis_dram_size,tbl_chassis.country,tbl_chassis. city,tbl_chassis.building,

tbl_chassis.other,tbl_chassis.status,tbl_chassis.c hasis_eos,tbl_chassis.chasis_eol,tbl_chassis.chasi s_user_field_1,tbl_chassis.chasis_user_field_2,tbl _chassis.chasis_user_field_3
from tbl_chassis tbl_chassis,tbl_card tbl_card WHERE
tbl_chassis.serial_no = tbl_card.serial_no AND lower(country) =
lower(trim('Singapore')) ORDER BY tbl_card.card_model asc


strawberry wrote:
we********@gmail.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size,
country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,building and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,building,other gave me the result which i not expected.

For example below data before sort

Country City Building Other
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 83 Aber rd
Thailand Bangkok
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark

After sort

Country City Building Other
Thailand Bangkok 83 Aber rd
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok

There's probably a better solution but here's one way:

SELECT country,city,building
FROM tbl_chassis
ORDER BY country,city,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(building
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;
strawberry wrote:
we********@gmail.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size,
country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,building and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,building,other gave me the result which i not expected.

For example below data before sort

Country City Building Other
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 83 Aber rd
Thailand Bangkok
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark

After sort

Country City Building Other
Thailand Bangkok 83 Aber rd
Thailand Bangkok 12 Godieon Rd
Thailand Bangkok 900 Telepark
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok 208 Wireless RD
Thailand Bangkok

There's probably a better solution but here's one way:

SELECT country,city,building
FROM tbl_chassis
ORDER BY country,city,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(building
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;
Aug 11 '06 #4
<snip>
we********@gmail.com wrote:
Hi ,

Thanks for your help , it works.

I have some other issue regarding sort in MYSQL .
<snip>

Read up on JOINs and ALIASES!

Anyway, this should work:

SELECT
DISTINCT(t1.serial_no),
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3,
t2.card_model
FROM tbl_chassis t1
LEFT JOIN tbl_card t2
USING (serial_no)
WHERE t1.country LIKE '%Singapore%'
ORDER BY t2.card_model asc;

Or, if you're determined not to have the card_model appear in the
results:

SELECT
t1.serial_no,
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3
FROM
(
SELECT
DISTINCT(t1.serial_no),
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3,
t2.card_model
FROM tbl_chassis t1
LEFT JOIN tbl_card t2
USING (serial_no)
WHERE t1.country LIKE '%Singapore%'
ORDER BY t2.card_model asc
) temp
;

Also, try to avoid posting responses 'above' those already submitted;
it's called 'top-posting' and it drives some NG aficionados nuts!

Aug 11 '06 #5
Hi ,

Thank for your help. Noted about cross-posting NG.

BTW , if i want to sort the query by t1.chasis_model then
t2.card_model ?
I changed the ORDER by as shown in the sql below, it did not gave me
the correct answer. Any ideas ? Thanks .

- weetat

SELECT
DISTINCT(t1.serial_no),
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3,
t2.card_model
FROM tbl_chassis t1
LEFT JOIN tbl_card t2
USING (serial_no)
WHERE t1.country LIKE '%Singapore%'
ORDER BY t1.chasis_model,t2.card_model asc;

What if I want to sort the
strawberry wrote:
<snip>
we********@gmail.com wrote:
Hi ,

Thanks for your help , it works.

I have some other issue regarding sort in MYSQL .
<snip>

Read up on JOINs and ALIASES!

Anyway, this should work:

SELECT
DISTINCT(t1.serial_no),
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3,
t2.card_model
FROM tbl_chassis t1
LEFT JOIN tbl_card t2
USING (serial_no)
WHERE t1.country LIKE '%Singapore%'
ORDER BY t2.card_model asc;

Or, if you're determined not to have the card_model appear in the
results:

SELECT
t1.serial_no,
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3
FROM
(
SELECT
DISTINCT(t1.serial_no),
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3,
t2.card_model
FROM tbl_chassis t1
LEFT JOIN tbl_card t2
USING (serial_no)
WHERE t1.country LIKE '%Singapore%'
ORDER BY t2.card_model asc
) temp
;

Also, try to avoid posting responses 'above' those already submitted;
it's called 'top-posting' and it drives some NG aficionados nuts!
Aug 11 '06 #6
<snip>Noted about cross-posting NG.</snip>

Noted, but misunderstood!!!

<snip>
I changed the ORDER by as shown in the sql below, it did not gave me
the correct answer. Any ideas ? Thanks .

- weetat

SELECT
DISTINCT(t1.serial_no),
t1.host_name,
t1.chasis_sw_version,
t1.chasis_model,
t1.chasis_flash_size,
t1.chasis_dram_size,
t1.country,
t1.city,
t1.building,
t1.other,
t1.status,
t1.chasis_eos,
t1.chasis_eol,
t1.chasis_user_field_1,
t1.chasis_user_field_2,
t1.chasis_user_field_3,
t2.card_model
FROM tbl_chassis t1
LEFT JOIN tbl_card t2
USING (serial_no)
WHERE t1.country LIKE '%Singapore%'
ORDER BY t1.chasis_model,t2.card_model asc;
</snip>

Looks OK to me. What results were you expecting?

Hint: to respond, start typing below this line
----------------------------------------------------------------

Aug 11 '06 #7

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

Similar topics

4
by: its me | last post by:
Let's say I have a class of people... Public Class People Public Sex as String Public Age as int Public Name as string end class And I declare an array of this class...
2
by: One's Too Many | last post by:
Ran into a strange problem today: 8.1.7 on AIX 4.3.3 Database and applications had been working fine for two years and all of a sudden a couple of regularly-run queries are now no longer...
20
by: Xah Lee | last post by:
Sort a List Xah Lee, 200510 In this page, we show how to sort a list in Python & Perl and also discuss some math of sort. To sort a list in Python, use the “sort” method. For example: ...
7
by: Steve Crawford | last post by:
I am suffering some sort order confusion. Given a database, "foo", with a single character(4) column of data left padded with spaces I get: select * from foo order by somechars; somechars...
2
by: adrian.chandler | last post by:
Hi all, I have been using letter and symbol codes such as GNU< GNU\ GNU} GNU in an Access table. I was surprised to see that when the table was sorted on this field, the order is: GNU...
5
by: Jan Smith | last post by:
I've searched the overloads for the Array.Sort method, and I haven't found a clear answer to my question. Maybe it's not in Array.Sort. Here's the question: I initialize an array X with the...
2
by: Patrick | last post by:
I got a page that loads a xml file into a treeview control. I want it to sort the data before sending it to the treeview control is that possible? <root> <level1 name="" src="" order="0">...
15
by: bcochofel | last post by:
Hi, I want to use a variable to sort elements. That var his passed with query string (I'm using Perl CGI to generate XML). Here's a sample of my output:...
3
by: aRTx | last post by:
I have try a couple of time but does not work for me My files everytime are sortet by NAME. I want to Sort my files by Date-desc. Can anyone help me to do it? The Script <? /* ORIGJINALI
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...

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.