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?
 
QlemoConnect With a Mentor 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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
QlemoBatchelor, 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

0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
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
0
 
QlemoBatchelor, 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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
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.
0
 
barnescoAuthor Commented:
Actually, it was Qlemo's second answer that is correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.