order by with union statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

order by with union statement

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-05 : 11:04:27

hi

based on this example.

Select ItemID, ProdName from table1
union
Select ProdID, ItemName from table1
order by ItemName

I need to because of the nature of the product name

How can i achieve an order by on the ItemName? Thanks a lot

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 11:22:25

so after all itemNames ProdName should come?


select ID,Val
from
(
Select ItemID AS ID, ProdName AS Val,1 as Ord from table1
union
Select ProdID, ItemName,0 from table1
)t
Order by Ord,Val

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-05 : 11:49:27

Sorry, I made a mistake on the SQL statement. It should be

Select ItemID, ProdName from table1
union
Select ProdID, ItemName from table2
order by ItemName

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 12:12:39

[code]
select ID,Val
from
(
Select ItemID AS ID, ProdName AS Val,1 as Ord from table1
union
Select ProdID, ItemName,0 from table2
)t
Order by Ord,Val
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-05 : 12:14:27

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 12:52:02

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-05 : 20:53:19

hi

I am not able to get the results. This is my resultset

ID PrtID Name
1 NULL A
2 NULL B
3 2 C
4 2 D
5 NULL E
1003 1 A4
1011 1 A5
1013 1 A6
1016 1 A1

The id from 1 to 5 is based on the first union query while the id from 1003 to 1016 is based from the second query.

As you notice, A4,A5,A6,A1 are not sorted by text.

How do i make the second query to be sorted so that i would get this. Thanks

ID PrtID Name
1 NULL A
2 NULL B
3 2 C
4 2 D
5 NULL E
1016 1 A1
1003 1 A4
1011 1 A5
1013 1 A6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 10:07:22

sorry this is different from what you posted before. you've an additional column. can you show the query used for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources