Solved

How to return the first EventName

Posted on 2013-12-22
8
293 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
OfficeMate Freezes on login or does not load after login credentials are input.
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.

766 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