How to return the first EventName

I'd like to return only the first EventName as in:

EventID      EventDateID      EventName
1              9                      Event 1
1             10      
2             11                      Event 2
2             12      
2             13      
2             14      
2             15      

Using:

Select
e.EventID,
ed.EventDateID,
e.EventName

FROM Event e INNER JOIN
      EventDate ed ON e.EventID = ed.EventID

How do I do this?
barnescoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Assuming that 'only the first EventName' means 'EventName for each EventID with the lowest EventDateID', and not 'non-NULL EventName for each EventID with the lowest EventDateID'..

SELECT e.EventID, e.EventDateID, e.EventName
FROM Event e
   JOIN (SELECT EventID, MIN(EventDateID) as event_date_min
            FROM Event
            GROUP BY EventID) emin ON e.EventID = emin.EventID AND e.EventDateID = emin.event_date_min

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The result should be one row (EventName = 'Event 1', ignoring the ID) or two (for IDs 1 and 2)?
The first one is really easy to do:
Select TOP 1 e.EventID, ed.EventDateID, e.EventName
FROM Event e INNER JOIN
      EventDate ed ON e.EventID = ed.EventID

Open in new window

barnescoAuthor Commented:
Jimhorn's answer looks the most promising, I understand the nested select statement, but I'm not sure of the rest. The SELECT statement still needs to draw from the EventDate table too (which the above did not), so I wrote the below, but I get the same results:

SELECT e.EventID, ed.EventDateID, e.EventName

FROM Event e JOIN
      EventDate ed ON e.EventID = ed.EventID INNER JOIN

(SELECT e.EventID, MIN(ed.EventDateID) AS Event_Date_MIN
FROM Event e INNER JOIN
      EventDate ed ON e.EventID = ed.EventID
GROUP BY e.EventID) emin
      ON e.EventID = emin.EventID AND ed.EventDateID = emin.event_date_min

Just to be clear, I get the below returned:

EventID      EventDateID      EventName
1               9                      Event 1
1               10                      Event 1
2               11                      Event 2
2               12                      Event 2
2               13                      Event 2
2               14                   Event 2
2               15                 Event 2

I want the below:

EventID      EventDateID      EventName
1               9                      Event 1
1               10                     
2               11                      Event 2
2               12                     
2               13                     
2               14                   
2               15
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Oh, I guess we got something wrong here. You do not want the first row of each ID, you want the event name to display only for the first row of each ID. That is something a reporting tool usually does. The according change to above query is:
SELECT e.EventID, ed.EventDateID,
     case when ed.EventDateID = Event_Date_MIN then e.EventName else null end AS EventName
FROM Event e JOIN EventDate ed ON e.EventID = ed.EventID
JOIN
  (SELECT e.EventID, MIN(ed.EventDateID) AS Event_Date_MIN
  FROM Event e INNER JOIN
        EventDate ed ON e.EventID = ed.EventID
  GROUP BY e.EventID) emin 
ON e.EventID = emin.EventID AND ed.EventDateID = emin.event_date_min

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
However, I would take a different approach:
select EventID, EventDateID,
    case when rn = 1 then EventName else null end as EventName
from (
  select e.EventID, ed.EventDateID, e.EventName,
      row_number() over (partition by e.EventID order by ed.EventDateID) as rn
  from Event e join EventDate ed on e.EventID = ed.EventID
) data

Open in new window

Jim HornSQL Server Data DudeCommented:
>Just to be clear, I get the below returned:
>I want the below:

It would greatly help if you could give us a mockup data set of what your data looks like right now, in addition to what you want it to look like.

Also, spell out the significance of the NULL values in EventName.

Qlemo is correct in that if you don't want repeating EventName values to display, that is typically handled by the reporting tool, as almost every reporting tool has some kind of 'Hide Duplicates' property.

In SSRS (versions 2008 R2 and after) a common trick I will use to cut down on the amount of data passed to the report is to have multiple datasets.  In your case, one would have EventID - EventDateID with multiple EventID's, and the other would have EventID - EventName with one row for each event, and a Lookup function to display the Event Name, with Hide Duplicates set to True.   This way, you're not passing a column of mostly NULLs, and not passing duplicate EventNave values.
barnescoAuthor Commented:
This works great.

I know that RS would have worked better, but the tool is unavailable to me to use.

My apologize for not being clearer. I thought I was but realized after the first answers I was not. I'll report before/after next time. Thanks for your help.
barnescoAuthor Commented:
Actually, it was Qlemo's second answer that is correct.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.