SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How Can I apply a SELECT Distinct With UNION ALL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jchoudja
Starting Member

USA
41 Posts

Posted - 04/02/2013 :  10:59:18  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/02/2013 :  11:55:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/02/2013 :  12:52:57  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/02/2013 :  13:03:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/02/2013 :  13:14:37  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 04/02/2013 13:15:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000