Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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?
0
barnesco
Asked:
barnesco
  • 3
  • 3
  • 2
1 Solution
 
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
 
QlemoC++ DeveloperCommented:
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
 
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now