SQL Query Distinct keyword issue

While executing join query I am getting duplicate data instead of th开发者_Python百科e "distinct" keyword. I dim much more google and gained nothing. The table names are "Event" which holds list of events and the other table is UserEvents which assgins vents to users. On event can be assigned to multiple users. I am using SQL server 2005.

Events
eid | ename
-----------------
e1  | Test event1
e2  | test ecent2

UserEvents
id| uid |  eventId
-----------------
1 | u1  |  e1
2 | u1  |  e2
3 | u2  |  e1

Query:

select distinct 
Events.eid, Events.ename,UserVents.uid  
  from
      Events
  inner join
      UserEvents on
      UserEvents.eventID=Events.eid

Output:

eid | ename       | uid
-------------------
e1  | Test event1 | u1
e2  | Test event2 | u2
e1  | test event1 | u1

Issue:

Here, Event is repeating instead of the distinct key word. It should not repeat the event 'e1". Kindly help me. How I change the query? Is this an issue in SQL server 2005?? kinly help

---

DISTINCT applies to the whole column list. The whole row needs to be the same to be eliminated.

The results you have put in your question are not complete as you have missed out the uid column.

WITH Events(eid,ename) AS(
SELECT 'e1' ,'Test event1' UNION ALL
SELECT 'e2' ,'test ecent2')

,UserEvents(id,uid,eventId) AS
(
SELECT 1,'u1' , 'e1' UNION ALL
SELECT 2,'u1' , 'e2' UNION ALL
SELECT 3,'u2' , 'e1'
)
select distinct 
        Events.eid, Events.ename,UserEvents.uid  
        from
        Events
        inner join
        UserEvents on
        UserEvents.eventId=Events.eid

Returns

eid  ename       uid
---- ----------- ----
e1   Test event1 u1
e1   Test event1 u2
e2   test ecent2 u1

None of the rows are the same.

---

Please, clarify your question, what result do you want to see. Your first query returned distincted result, you can check it on SE data explorer here https://data.stackexchange.com/stackoverflow/qt/115261 From MSDN: DISTINCT Specifies that only unique rows can appear in the result set.

---

if the distinct keyword is not working try adding a group by clause. Your new query should look like:

select  
            Events.eid, Events.ename,UserVents.uid  
            from
            Events
            inner join
            UserEvents on
            UserEvents.eventID=Events.eid
group by Events.eid, Events.ename,UserVents.uid

---

It should not repeat the event 'e1

How I change the query?

This might be what you are looking for:

select E.eid, 
       E.ename, 
       E.uid
from (
      select Events.eid, 
             Events.ename,
             UserEvents.uid,
             row_number() over(partition by eid 
                               order by UserEvents.uid) as rn  
      from Events
        inner join UserEvents 
          on UserEvents.eventId=Events.eid
     ) as E
where E.rn = 1 

Result:

eid  ename       uid
---- ----------- ----
e1   Test event1 u1
e2   test ecent2 u1    

Try it on SE Data: https://data.stackexchange.com/stackoverflow/q/115257/