By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,706 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,706 IT Pros & Developers. It's quick & easy.

SQL sort (Order by)

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a

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

P: n/a

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

P: n/a
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

P: n/a
<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

P: n/a
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

P: n/a
<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 discussion thread is closed

Replies have been disabled for this discussion.