473,712 Members | 2,632 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL sort (Order by)

Hi all ,

I have sql statement below ,
SELECT
serial_no,host_ name,chasis_mod el,chasis_flash _size,chasis_dr am_size,
country,city,bu ilding,other,ch asis_sw_version ,status,chasis_ eos,chasis_eol, chasis_user_fie ld_1,chasis_use r_field_2,chasi s_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,bu ilding and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,bu ilding,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 8836

we********@gmai l.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_ name,chasis_mod el,chasis_flash _size,chasis_dr am_size,
country,city,bu ilding,other,ch asis_sw_version ,status,chasis_ eos,chasis_eol, chasis_user_fie ld_1,chasis_use r_field_2,chasi s_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,bu ilding and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,bu ilding,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,bu ilding
FROM tbl_chassis
ORDER BY
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(buildin g
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;

Aug 10 '06 #2

we********@gmai l.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_ name,chasis_mod el,chasis_flash _size,chasis_dr am_size,
country,city,bu ilding,other,ch asis_sw_version ,status,chasis_ eos,chasis_eol, chasis_user_fie ld_1,chasis_use r_field_2,chasi s_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,bu ilding and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,bu ilding,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,bu ilding
FROM tbl_chassis
ORDER BY country,city,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(buildin g
,'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_ch assis.serial_no ),tbl_chassis.h ost_name,tbl_ch assis.chasis_sw _version,tbl_ch assis.chasis_mo del,tbl_chassis .chasis_flash_s ize,tbl_chassis .chasis_dram_si ze,tbl_chassis. country,tbl_cha ssis.city,tbl_c hassis.building ,

tbl_chassis.oth er,tbl_chassis. status,tbl_chas sis.chasis_eos, tbl_chassis.cha sis_eol,tbl_cha ssis.chasis_use r_field_1,tbl_c hassis.chasis_u ser_field_2,tbl _chassis.chasis _user_field_3
from tbl_chassis tbl_chassis,tbl _card tbl_card WHERE
tbl_chassis.ser ial_no = tbl_card.serial _no AND lower(country) =
lower(trim('Sin gapore')) ORDER BY tbl_card.card_m odel asc


strawberry wrote:
we********@gmai l.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_ name,chasis_mod el,chasis_flash _size,chasis_dr am_size,
country,city,bu ilding,other,ch asis_sw_version ,status,chasis_ eos,chasis_eol, chasis_user_fie ld_1,chasis_use r_field_2,chasi s_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,bu ilding and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,bu ilding,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,bu ilding
FROM tbl_chassis
ORDER BY country,city,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(buildin g
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;
strawberry wrote:
we********@gmai l.com wrote:
Hi all ,

I have sql statement below ,
SELECT
serial_no,host_ name,chasis_mod el,chasis_flash _size,chasis_dr am_size,
country,city,bu ilding,other,ch asis_sw_version ,status,chasis_ eos,chasis_eol, chasis_user_fie ld_1,chasis_use r_field_2,chasi s_user_field_3
FROM tbl_chassis ORDER BY country = '', country
However , my user would like to sort by country,city,bu ilding and other
columns .
Anybody have ideas how to do that ? I do order by
country,city,bu ilding,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,bu ilding
FROM tbl_chassis
ORDER BY country,city,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(buildin g
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
,'0','')
;
Aug 11 '06 #4
<snip>
we********@gmai l.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.ser ial_no),
t1.host_name,
t1.chasis_sw_ve rsion,
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_ve rsion,
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.ser ial_no),
t1.host_name,
t1.chasis_sw_ve rsion,
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.ser ial_no),
t1.host_name,
t1.chasis_sw_ve rsion,
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********@gmai l.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.ser ial_no),
t1.host_name,
t1.chasis_sw_ve rsion,
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_ve rsion,
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.ser ial_no),
t1.host_name,
t1.chasis_sw_ve rsion,
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.ser ial_no),
t1.host_name,
t1.chasis_sw_ve rsion,
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
3758
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
3215
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 coming out in correct sort order specified in the ORDER clauses of the select statements. Behavior is erratic, about half the time the sort order is correct, and the other half is not. All expected rows are being returned (no data is missing) and the...
20
4068
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: li=;
7
7536
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 ----------- 0 1
2
7767
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 GNU_ GNU} GNU< I was expecting:
5
2840
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 values 28 142 3 17 225. I can sort this array in ascending order and it will return 3 17 28 142 225. But I want a method that will return the sort order, not the array in sorted
2
2200
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"> <level1.2 .. order="5"> <level1.2 .. order="3"> <level1.2 .. order="2"> <level1 .. order="2"> <level1 .. order="1">
15
2795
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: --------------------------------------------- <?xml version="1.0" encoding="iso-8859-1"?> <?xml-stylesheet type="text/xml" href="RR.xsl"?> <!-- $Id: template.xml,v 1.5 2006/12/11 11:13:30 bcochofel Exp $ --> <RR xmlns:xsi="http://www.w3.org/2001/XMLSchema"...
3
5189
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
8794
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
8701
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9300
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9007
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6620
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4459
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4713
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3154
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2509
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.