MS access SQL select two fields and ordered by both fields at the same time

Question

I am using Access to deal with a table with two fields. The table looks like

Field1   Field2
  bob      john
  kate     jum
  tim      kim

I want to select both fields and output them in order, but I can't use

Select Field1,Field2
From table1
order by Field1,Field2;

Because I want to sort them by the result of concatenation of Field1 and Filed2, but not order by Field1 first then Field2 secondly. What to do please? thanks.

Your question doesn't make any sense; you order by one column, then a second column. Are you trying to select Field1 and Field2 as a single column? Show your expected output results. — LittleBobbyTables - Au Revoir, Oct 03 '14 at 15:53
Do you want to sort by result of concatenation of Field1 and Filed2? — a1ex07, Oct 03 '14 at 15:56
Thanks for reply. Yes, I want to sort the result of concatenation of both fields. — user3684119, Oct 03 '14 at 16:16

score 1 · Accepted Answer · answered Oct 03 '14 at 15:55

1

SELECT Field1 FROM table1
UNION
SELECT Field2 FROM table1

use UNION ALL if you want to keep duplicate names

answered Oct 03 '14 at 15:55

Barry

3,683
1
18
25

Assuming that's their intent, you're not sorting the results – LittleBobbyTables - Au Revoir Oct 03 '14 at 15:56
Are you sure about that? http://stackoverflow.com/questions/421049/sql-server-union-what-is-the-default-order-by-behaviour – LittleBobbyTables - Au Revoir Oct 03 '14 at 15:59
When you do this for one field, it will be sorted as expected. [Check this](http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union). With multiple fields I do not have a clue :). – Barry Oct 03 '14 at 16:03
SO disagrees: http://stackoverflow.com/questions/15766359/does-union-all-guarantee-the-order-of-the-result-set – LittleBobbyTables - Au Revoir Oct 03 '14 at 16:06
I can only speak from experience. Never had a problem (Oracle, SQL Server, MySql and Access). But there is a first time for everything. But I think he wants to display the two fields, both ordered. By chance they are already sorted in his example. I expect that is his expected output. – Barry Oct 03 '14 at 16:10
Thanks a lot for this. It works. Using union automatically sort the fields. – user3684119 Oct 03 '14 at 16:22

score 0 · Answer 2 · answered Oct 03 '14 at 15:56

0

I would add an autonumbered field to the table. Then you can order by that field.

answered Oct 03 '14 at 15:56

Mr. Mascaro

2,693
1
11
18

MS access SQL select two fields and ordered by both fields at the same time

2 Answers2