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 6 8716 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','')
; 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','')
;
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','')
;
<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!
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!
<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
---------------------------------------------------------------- This discussion thread is closed Replies have been disabled for this discussion. Similar topics
4 posts
views
Thread by its me |
last post: by
|
2 posts
views
Thread by One's Too Many |
last post: by
|
20 posts
views
Thread by Xah Lee |
last post: by
|
7 posts
views
Thread by Steve Crawford |
last post: by
|
2 posts
views
Thread by adrian.chandler |
last post: by
|
5 posts
views
Thread by Jan Smith |
last post: by
|
2 posts
views
Thread by Patrick |
last post: by
|
15 posts
views
Thread by bcochofel |
last post: by
| | | | | | | | | | | |