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
 General SQL Server Forums
 New to SQL Server Programming
 How Can I apply a SELECT Distinct With UNION ALL

Author  Topic 

jchoudja
Starting Member

41 Posts

Posted - 2013-04-02 : 10:59:18
Hi,

Is it possible make a SELECT DISTINCT base on 2 Queries United by UNION ALL

example

SELECT ID, Name, DateIn, TimeIn FROM Table1
UNION ALL
SELECT ID, Name, DateIn, TimeIn From Table2
ORDER BY ID, DateIn,TimeIn;

I want a select a distinct value on ID from both queries
How is this possible?

Thank you.

jc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 11:55:17
if you're not concerned on which one you select, do like


SELECT ID,Name, DateIn,TimeIn
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TimeIn DESC) AS Seq,*
FROM
(
SELECT ID, Name, DateIn, TimeIn FROM Table1
UNION ALL
SELECT ID, Name, DateIn, TimeIn From Table2
)t
)r
WHERE Seq=1
ORDER BY ID, DateIn,TimeIn;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-02 : 12:52:57
Or perhaps you may need

SELECT ID, Name, DateIn, TimeIn FROM Table1
UNION
SELECT ID, Name, DateIn, TimeIn From Table2

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 13:03:25
quote:
Originally posted by MIK_2008

Or perhaps you may need

SELECT ID, Name, DateIn, TimeIn FROM Table1
UNION
SELECT ID, Name, DateIn, TimeIn From Table2

Cheers
MIK


it wont ensure uniqueness of ID values unless all other values are same


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-02 : 13:14:37
quote:
Originally posted by visakh16

quote:
Originally posted by MIK_2008

Or perhaps you may need

SELECT ID, Name, DateIn, TimeIn FROM Table1
UNION
SELECT ID, Name, DateIn, TimeIn From Table2

Cheers
MIK


it wont ensure uniqueness of ID values unless all other values are same


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Correct :)

Cheers
MIK
Go to Top of Page
   

- Advertisement -