December 28, 2016 at 9:31 pm
Hi,
Below is the table and results I am looking for......i am an SQL newbie.
Game Play_IdTeam PlayerEVENT
1 1
1 2 A Frank3
1 3 A John2
1 4 B Michael4
2 1
2 2 A Craig
2 3 B Bill 2
2 4 A Tom1
the result i am looking for is the first event for each team in each game where the event is not null.
the result of the above would be:
GamePlay Id TeamPlayerEvent
12 AFrank 3
14 BMichael4
23 BBill 2
24 ATom 1
December 29, 2016 at 12:10 am
hello, please try this code.
create table #tmp( Game VARCHAR(100),PlayId VARCHAR(100),Team VARCHAR(100),Player VARCHAR(100),[Event] VARCHAR(10))
insert into #tmp(Game,PlayId,Team,Player,[Event])
values(1,1,'','',''),
(1,2,'A','Frank',3),
(1,3,'A','John',2),
(1,4,'B','Michael',4),
(2,1,'','',''),
(2,2,'A','Craig',''),
(2,3,'B','Bill',2),
(2,4,'A','Tom',1)
select A.Game,PlayId,A.Team,Player,[Event] from(
select Game,PlayId,Team,Player,[Event],Row_number() over( order by Game) as Ord1 from #tmp where [Event]<>0
) A
inner join(
select Game,Team,min(ord) as ord from (
select Game,PlayId,Team,Player,[Event],Row_number() over( order by Game) as Ord
from #tmp where [Event]<>0
) A group by Game,Team
) B on A.Ord1=B.ord
Thanks
Sneh
December 29, 2016 at 2:48 am
Going to fix a few bits first.
Firstly, to the above poster, the OP stated that they were using NULL's, not blank strings. I am also unsure why you have chosen to use VARCHAR(100) for INTs. I have corrected the CREATE statement as follows:
CREATE TABLE #tmp (Game INT,
PlayID INT,
Team CHAR(1),
Player VARCHAR(15),
[Event] INT);
INSERT INTO #tmp (Game, PlayID, Team, Player, [Event])
VALUES (1,1,NULL,NULL,NULL),
(1,2,'A','Frank',3),
(1,3,'A','John',2),
(1,4,'B','Michael',4),
(2,1,NULL,NULL,NULL),
(2,2,'A','Craig',NULL),
(2,3,'B','Bill',2),
(2,4,'A','Tom',1);
In the solution above, you are also checking for if the events has a value of 0 and excluding them. You're inserting a blank string in your INSERT, which as a result of not declaring your INT as strings is inserting a 0. You really need to be consistent. If you're inserting INTs, why did you declare [Event] as an VARCHAR(10)? I would strongly suggest to the OP that this solution will NOT work on your environment.
You achieve this much more cleanly (and to specification) by using a CTE instead. You won't need to scan the table twice as with the above solution. I would also, however, caution that the player being returned is effectively "random". There is no ordering, so it's "luck" at the moment, however, if you have any indexing it will likely change the order.
WITH CTE (Game, PlayID, Team, Player, [Event], EventNum) AS (
SELECT t.Game,
t.PlayId,
t.Team,
t.Player,
t.[Event],
ROW_NUMBER() OVER (PARTITION BY Game, Team ORDER BY Game) AS EventNum --Note; the returned ordering here for which player is not guaranteed.
FROM #tmp t
WHERE t.[Event] IS NOT NULL
)
SELECT C.Game,
C.PlayID,
C.Team,
C.Player,
C.[Event]
FROM CTE C
WHERE C.EventNum = 1
ORDER By C.Game,
C.PlayID;
DROP TABLE #tmp;
I would suggest some type of ordering or ID on the player, so you can guarantee that the correct one returns (if it matters). For example, if a player, 'Steve' were in Team A, in event 1, Play 4, then he may return over Tom.
EDIT: There was a typo that was bugging me 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 29, 2016 at 4:32 pm
Hi,
Thank you both for attempting to solve my problem. I appreciate the help.
Thom A - The solution you have posted, returns the correct team/player combination for the first team, but as you said the 2nd teams return isn't the first occurrence, it actually is getting the last occurrence for the 2nd team. It's not random as you suggested.
In the full data table I have, there are 600 play id's for the first group of "game". The code is grabbing the 598th play id for the 2nd team which is the last valid combination for the 2nd team. The first team is correct and is returning the first occurrence.
What I would like is the first occurrence of the 2nd team as well as the first occurrence for the 1st team which the code is already returning.. I will try and play around with ordering.
FYI....The playId is always unique for any game.
December 29, 2016 at 4:52 pm
I worked it out 🙂
As you suggested, it just needed a slight modification to "order by" in the below section of the code:
ROW_NUMBER() OVER (PARTITION BY Game, Team ORDER BY Game) AS EventNum
I added PlayID to the ORDER BY.
so it now reads:
ROW_NUMBER() OVER (PARTITION BY Game, Team ORDER BY Game, PlayID) AS EventNum
thanks so much for your assistance and I have marked your reply as the solution 🙂 Have a great day.
December 30, 2016 at 8:11 am
watto84 (12/29/2016)
I worked it out 🙂As you suggested, it just needed a slight modification to "order by" in the below section of the code:
ROW_NUMBER() OVER (PARTITION BY Game, Team ORDER BY Game) AS EventNum
I added PlayID to the ORDER BY.
so it now reads:
ROW_NUMBER() OVER (PARTITION BY Game, Team ORDER BY Game, PlayID) AS EventNum
thanks so much for your assistance and I have marked your reply as the solution 🙂 Have a great day.
There's no reason to include a PARTITION expression in the ORDER BY clause (in this case "Game"). By definition, all rows in a partition have the same values for all of the partition expressions. Sorting by a field that does not change cannot affect the order in any meaningful way, so just leave it out.
ROW_NUMBER() OVER (PARTITION BY Game, Team ORDER BY PlayID) AS EventNum
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2017 at 9:45 am
>> Below is the table and results I am looking for......i am an SQL newbie. <<
please read the forum rules about how to do a posting. Your narrative should have been DDL! Now because of your bad manners and lack of netiquette, we have to guess at everything and retype everything for you. Does your teacher or boss make you do this when you are at work or school? I hope not.
Here is my guess.
CREATE TABLE Games
(game_nbr INTEGER NOT NULL,
play_nbr INTEGER NOT NULL,
PRIMARY KEY (game_nbr, play_nbr),
team_name CHAR(1),
player_name VARCHAR(10),
event_nbr INTEGER);
INSERT INTO Games
VALUES
(1, 1, NULL, NULL, NULL),
(1, 2, ‘A’, ‘Frank’, 3),
(1, 3, ‘A’, ‘John’, 2),
(1, 4, ‘B’, ‘Michael’, 4),
(2, 1, NULL, NULL, NULL),
(2, 2, ‘A’, ‘Craig’, NULL),
(2, 3, ‘B’, ‘Bill’, 2),
(2, 4, ‘A’, ‘Tom’, 1),
did you notice that your last 3 columns seem to be null-able? I guess that is what a blank space in a narrative means. This is an unusually high amount of null-able columns. There is a bunch of other design flaws in this, but let us skip them for now.
>> the result I am looking for is the first event for each team in each game where the event is not null. <<
Do you understand that, SQL is based on sets, so unless you explicitly define an ordering it does not exist on the table? Where is the code you attempted before you ask us to do your job for you?
the result of the above would be:
WITH X1
AS
(SELECT game_nbr, play_nbr, team_name, player_name, event_nbr
FROM Games
WHERE event_nbr IS NOT NULL),
X2
AS
(SELECT X1.game_nbr, play_nbr, team_name, player_name, event_nbr,
MIN(X1.play_nbr)
OVER (PARTITION BY X1.game_nbr) AS first_play
FROM X1)
SELECT X2.*
FROM X2
WHERE X2.first_play = X2.play_nbr
I broke this down into CTE’s so you can see the logic.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 4, 2017 at 4:38 pm
Celko -
I'm really sorry someone has (obviously) wronged you in your past that you feel the need to continually respond in a condescending manner to people asking for help on this forum. When someone has an attitude such as yours, it is going to make others think twice before posting their question so as not to get belittled. That, sir, does a disservice to this community. The OP stated they were a NEWBIE. Is this how you mentor others? Have you no decorum or class??
January 5, 2017 at 11:43 am
I have been teaching SQL for over 30 years. In that time I found that if you do not give a newbie all the information that they need, they tend to keep repeating the same mistakes. You also need to give them a reference for why what they have done is mistake.
Failure to post DDL is not a matter of being a newbie. This requirement is given at the front of every SQL forum I have ever posted on, because we need it. If the poster cannot post DDL, even a simple skeleton, then why do you think you are going to be able to give them an answer that will help them?
Usually people who ask for this kind of help are actually fishing for someone to do their homework for them. This is why such posters do not like to show us the DML they attempted before going to a forum.I think you will find the usual situation is they did not try anything at all!
Did you notice that I gave my answer with CTE rather than a nested query? This lets the poster see how the pieces work, layer by layer, rather than as a whole. He can execute it, one part one part at a time, and see the intermediate results.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply