SQL sort (Order by)

Hi all ,

I have sql statement below ,


SELECT
serial_no,host_name,chasis_model,chasis_flash_size,chasis_dr am_size,
country,city,building,other,chasis_sw_version,status,chasis_ eos,chasis_eol,chasis_user_field_1,chasis_user_field_2,chasi s_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

Re: SQL sort (Order by)

weetat.yeo@gmail.com wrote:
> Hi all ,
>
> I have sql statement below ,
>
>
> SELECT
> serial_no,host_name,chasis_model,chasis_flash_size,chasis_dr am_size,
> country,city,building,other,chasis_sw_version,status,chasis_ eos,chasis_eol,chasis_user_field_1,chasis_user_field_2,chasi s_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','')
;

Re: SQL sort (Order by)

weetat.yeo@gmail.com wrote:
> Hi all ,
>
> I have sql statement below ,
>
>
> SELECT
> serial_no,host_name,chasis_model,chasis_flash_size,chasis_dr am_size,
> country,city,building,other,chasis_sw_version,status,chasis_ eos,chasis_eol,chasis_user_field_1,chasis_user_field_2,chasi s_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','')
;

Re: SQL sort (Order by)

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_name,tbl_ch assis.chasis_sw_version,tbl_chassis.chasis_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.chasis_eos, tbl_chassis.chasis_eol,tbl_chassis.chasis_user_field_1,tbl_c hassis.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:
> weetat.yeo@gmail.com wrote:
> > Hi all ,
> >
> > I have sql statement below ,
> >
> >
> > SELECT
> > serial_no,host_name,chasis_model,chasis_flash_size,chasis_dr am_size,
> > country,city,building,other,chasis_sw_version,status,chasis_ eos,chasis_eol,chasis_user_field_1,chasis_user_field_2,chasi s_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:
> weetat.yeo@gmail.com wrote:
> > Hi all ,
> >
> > I have sql statement below ,
> >
> >
> > SELECT
> > serial_no,host_name,chasis_model,chasis_flash_size,chasis_dr am_size,
> > country,city,building,other,chasis_sw_version,status,chasis_ eos,chasis_eol,chasis_user_field_1,chasis_user_field_2,chasi s_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','')
> ;

Re: SQL sort (Order by)

weetat.yeo@gmail.com wrote:
> Hi ,
>
> Thanks for your help , it works.
>
> I have some other issue regarding sort in MYSQL .
>


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!

Re: SQL sort (Order by)

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:
>
> weetat.yeo@gmail.com wrote:
> > Hi ,
> >
> > Thanks for your help , it works.
> >
> > I have some other issue regarding sort in MYSQL .
> >
>
>
> 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!

Re: SQL sort (Order by)

Noted about cross-posting NG.

Noted, but misunderstood!!!


> 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;


Looks OK to me. What results were you expecting?

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