April 28, 2016 at 11:43 pm
Hi All,
I have a Table which has Columns ID,IDDATE,JID,FROMID,TOID
Values Below
SELECT721 AS ID,42401 AS IDDATE,1 JID,'A' FROMID,'B' TOID
UNION SELECT721,42401,2,'C','D'
UNION SELECT721,42401,2,'E','F'
UNION SELECT721,42401,3,'G','H'
UNION SELECT721,42401,4,'I','J'
UNION SELECT721,42401,5,'K','L'
UNION SELECT722,42767,2,'A','B'
UNION SELECT722,42767,3,'C','D'
UNION SELECT722,42767,4,'E','F'
UNION SELECT722,42767,5,'G','H'
UNION SELECT722,42767,6,'I','J'
UNION SELECT722,42767,7,'K','L'
UNION SELECT722,42767,8,'M','N'
UNION SELECT722,42767,9,'O','P'
UNION SELECT722,42767,10,'Q','R'
Objective : I need to Find the FROMID FOR MIN OF JID AND TOID MAXOF JID FOR EACH ID,IDDATE COMBINATION
EXPECTED RESULT :
1. FOR ID : 721 AND IDDATE : 42401 FROMID : A (AS MIN OF JID IS 1) TOID : L (MAX OF JID IS 5)
2. FOR ID : 722 AND IDDATE : 42767 FROMID : A (AS MIN OF JID IS 2) TOID : ( (MAX OF JID IS 10)
Can you please help me with the Query
April 28, 2016 at 11:59 pm
So, what's the problem with this task?
Are you able to google "GROUP BY"?
_____________
Code for TallyGenerator
April 29, 2016 at 2:14 am
You could use the ROW_number window function in a cte and then do a self join on it:
WITH CTE AS
(
SELECT ID, IDDATE, JID, FROMID, TOID
, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY JID asc) MinJID
, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY JID desc) MaxJID
FROM (Values
(721,42401,1,'A','B'),
(721,42401,2,'C','D'),
(721,42401,2,'E','F'),
(721,42401,3,'G','H'),
(721,42401,4,'I','J'),
(721,42401,5,'K','L'),
(722,42767,2,'A','B'),
(722,42767,3,'C','D'),
(722,42767,4,'E','F'),
(722,42767,5,'G','H'),
(722,42767,6,'I','J'),
(722,42767,7,'K','L'),
(722,42767,8,'M','N'),
(722,42767,9,'O','P'),
(722,42767,10,'Q','R')) Vals (ID, IDDATE, JID, FROMID, TOID)
)
SELECT A.ID, A.IDDATE, A.FROMID, B.TOID FROM CTE A
INNER JOIN CTE B
ON A.ID = B.ID AND A.MinJID = 1 AND B.MaxJID = 1
April 29, 2016 at 2:44 am
Or this - probably more efficient since it only involves a single scan:
SELECT DISTINCT
ID
,IDDATE
,FIRST_VALUE(FROMID) OVER (PARTITION BY ID, IDDATE ORDER BY JID) AS FROMID
,FIRST_VALUE(TOID) OVER (PARTITION BY ID, IDDATE ORDER BY JID DESC) AS TOID
FROM (Values
(721,42401,1,'A','B'),
(721,42401,2,'C','D'),
(721,42401,2,'E','F'),
(721,42401,3,'G','H'),
(721,42401,4,'I','J'),
(721,42401,5,'K','L'),
(722,42767,2,'A','B'),
(722,42767,3,'C','D'),
(722,42767,4,'E','F'),
(722,42767,5,'G','H'),
(722,42767,6,'I','J'),
(722,42767,7,'K','L'),
(722,42767,8,'M','N'),
(722,42767,9,'O','P'),
(722,42767,10,'Q','R')) Vals (ID, IDDATE, JID, FROMID, TOID)
John
April 29, 2016 at 7:28 am
John Mitchell-245523 (4/29/2016)
Or this - probably more efficient since it only involves a single scan:
SELECT DISTINCTID
,IDDATE
,FIRST_VALUE(FROMID) OVER (PARTITION BY ID, IDDATE ORDER BY JID) AS FROMID
,FIRST_VALUE(TOID) OVER (PARTITION BY ID, IDDATE ORDER BY JID DESC) AS TOID
FROM (Values
(721,42401,1,'A','B'),
(721,42401,2,'C','D'),
(721,42401,2,'E','F'),
(721,42401,3,'G','H'),
(721,42401,4,'I','J'),
(721,42401,5,'K','L'),
(722,42767,2,'A','B'),
(722,42767,3,'C','D'),
(722,42767,4,'E','F'),
(722,42767,5,'G','H'),
(722,42767,6,'I','J'),
(722,42767,7,'K','L'),
(722,42767,8,'M','N'),
(722,42767,9,'O','P'),
(722,42767,10,'Q','R')) Vals (ID, IDDATE, JID, FROMID, TOID)
John
When using FIRST_VALUE, you should ALWAYS specify the window frame, because the default is RANGE UNBOUNDED PRECEDING which always writes to a work table, whereas ROWS UNBOUNDED PRECEDING will only write to a work table if the number of rows exceeds 10000 records per partition and FIRST_VALUE will always have exactly two rows per partition.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 29, 2016 at 7:39 am
Thanks Drew. I didn't know that. It doesn't seem to make a difference to the execution plan in this particular case, but I'll certainly bear it in mind in future.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply