Help - UNION Sort - 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 2012 Forums

Transact-SQL (2012)

Help - UNION Sort

Author

Topic

PeetKoekemoer
Starting Member

12 Posts

Posted - 2013-08-26 : 06:41:00

Hi Please assist me with the following query sorting.

I have two tables (Main Member) and (Additional Member) both tables structure are exactly the same. I now try to create a report to display a complete membership list and using UNION to display the data, but I have a problem with the sort. The sort should work like this
- [Main Member].[Main Member Surname]
- [Main Member].[Family Membership Number]
- [Additional Member].[ID Number]
(Basically it should Short according to the main members surname, then add all the additional members linked to the family membership number under the main member although their surnames can be differ)

This is my current query that does not work if the additional member's surname is different than the main member's

*****
SELECT

[Family Membership Number],
[Member Type],
[Title],
[Name],
[Surname],
[ID Nommer],
[Physical Address],
[Postal Address],
[E-Mail Address],
[Tel No (H):]
FROM
vHoofLede
Where
Name <>''

union

SELECT
[Family Membership Number],
[Member Type],
[Title],
[Name],
[Surname],
[ID Nommer],
[Physical Address],
[Postal Address],
[E-Mail Address],
[Tel No (H):]

FROM
vAdditioneleLede
Where
[Name] <>''
Order by
[Surname], [Family Membership Number], [Member Type], [ID Nommer]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-26 : 06:59:50

Can you provide some sample data for your scenario and expected output?

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-26 : 07:14:16

[code]with cte
as (
SELECT [Family Membership Number],
[Member Type],
[Title],
[Name],
[Surname],
[ID Nommer],
[Physical Address],
[Postal Address],
[E-Mail Address],
[Tel No (H):]
FROM vHoofLede
Where Name <>''

union

SELECT [Family Membership Number],
[Member Type],
[Title],
[Name],
[Surname],
[ID Nommer],
[Physical Address],
[Postal Address],
[E-Mail Address],
[Tel No (H):]
FROM vAdditioneleLede
Where [Name] <>''
)
SELECT *
FROM cte
Order by [Surname],
[Family Membership Number],
[Member Type],
[ID Nommer][/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources