How to sort SQL table based on a column value?

Question

I have two tables

Name  |  Age
------------
a     |   20
b     |   15


Name  | Age(varchar)
------------------
e     |  two
d     |   one

then i want to show

Name   | Age
-----------
b      |  15
a      |  20
e      |  two
d      | one

How ?

I dont see what you are trying to sort, it looks more like a union? — Matt
– Matt, Commented Jul 23, 2015 at 10:01

Praveen · Accepted Answer · 2015-07-23 10:28:08Z

1

This can be done by union / union all operation, but the data type of Age column of these tables seems different.

so if you try to union all these tables you get an error saying;

Conversion failed when converting the varchar value 'two' to data type int

So convert the data type of age column of table 1 to varchar before union them

select id, age from (
    select id, convert(varchar(20), age) as age from tbl1
    union all
    select id, age from tbl2 
) as x
order by age

If you are OK with duplicate data use union all else union (union have additional overhead of removing duplicate data)

Note: order by age order age lexicographically

answered Jul 23, 2015 at 10:28

Praveen

9,4534 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

luke_t · Accepted Answer · 2015-07-23 10:06:14Z

0

As stated by @Matt in the comments, this does like look you wish to perform a UNION query, see msdn for an explanation on performing a UNION query.

SELECT *
FROM Table1
UNION
SELECT *
FROM Table2

Edit: if you wanted to order the results, you would then just add the ORDER BY keyword into the query.

SELECT *
FROM Table1
UNION
SELECT *
FROM Table2
ORDER BY Name

answered Jul 23, 2015 at 10:06

luke_t

2,9874 gold badges26 silver badges39 bronze badges

HashSu · Accepted Answer · 2015-07-23 10:22:03Z

0

SELECT *
FROM Table1
UNION
SELECT *
FROM Table2
ORDER BY Name, Age;

answered Jul 23, 2015 at 10:22

HashSu

1,5271 gold badge13 silver badges14 bronze badges