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 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','')
; 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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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:
...
|
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...
|
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...
|
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...
|
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">...
|
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:...
|
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
|
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"....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
| |