Solved

How to return the first EventName

Posted on 2013-12-22
8
297 Views
Last Modified: 2013-12-23
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
Comment
Question by:barnesco
  • 3
  • 3
  • 2
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39734952
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 39734955
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
 

Author Comment

by:barnesco
ID: 39735300
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39735721
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 39735736
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39735934
>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
 

Author Closing Comment

by:barnesco
ID: 39736481
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
 

Author Comment

by:barnesco
ID: 39736496
Actually, it was Qlemo's second answer that is correct.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Resolve DNS query failed errors for Exchange
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question