Getting a Last-type value from a union query 1

[cascot]

If I have SQL something like the following

(
SELECT Something.SomethingId, [Surname] + ', ' + [FirstName] AS MyPerson
FROM Blah Blah Blah
WHERE (((Table.SomeId)=1234))

UNION

SELECT Something_1.SomethingId, [Surname] + ', ' + [FirstName] AS MyPerson
FROM Blah Blah Blah
WHERE (((Table.SomeId)=1234))
)
ORDER BY SomethingId

Let's say it returns 2 records as it stands...

SomethingId MyPerson
1 John Smith
99 Able Andy

Is there somthing else I can add to the SQL so it will return only one record with the name of MyPerson related to the Maximum SomethingId?

 

[AWithers]

One way is to insert the data from the two seperate queries into a temporary table and then select just the top 1 from the temporary table

Andy

create table ##tempdata (....)

insert into ##tempdata
....

insert into ##tempdata
....

select top 1 from ##tempdata
order by SomethingId


drop table ##tempdata

 

[cascot]

AWithers,

Thanks for your reply.

That would certainly be a solution, but unfortunately I need a solution that can form part of a much larger and more complex select query. It may not be possible to do it at all.

 

[JohnDTampaBay]

Or use this query as a derived table and wrap it in the SELECT TOP 1. Kind of like this:

SELECT TOP 1 x.SomethingID, x.MyPerson
FROM (
  SELECT Something.SomethingId, [Surname] + ', ' +
    [FirstName] AS MyPerson
  FROM Blah Blah Blah WHERE (((Table.SomeId)=1234))
 UNION
  SELECT Something_1.SomethingId, [Surname] + ', ' +
    [FirstName] AS MyPerson
  FROM Blah Blah Blah WHERE (((Table.SomeId)=1234))
 ORDER BY SomethingId
 ) AS x

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[cascot]

AngelWPB,

Thanks for your reply, it helped me get the solution with just a little tweak

Having the ORDER BY where it is caused an error, I also wanted the value against the record with the highest SomethingId so I ended up with this

SELECT TOP 1 x.SomethingID, x.MyPerson
FROM (
SELECT Something.SomethingId, [Surname] + ', ' +
[FirstName] AS MyPerson
FROM Blah Blah Blah WHERE (((Table.SomeId)=1234))
UNION
SELECT Something_1.SomethingId, [Surname] + ', ' +
[FirstName] AS MyPerson
FROM Blah Blah Blah WHERE (((Table.SomeId)=1234))
) AS x
ORDER BY SomethingId DESC

Thanks again for your help.