September 3, 2014 at 1:45 pm
I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
Any help would be appreciated.
I apologize for the formatting I don't know how to add tables in here.
Thanks.
September 3, 2014 at 1:56 pm
This worked... I changed a few field names, because they're reserved in T-SQL. Turns out this is a plain totals query.
SELECT Ticket, UserX, MIN(PriorityX)
FROM
(SELECT 'A' AS Ticket,
'ME' As UserX,
1 AS PriorityX
UNION ALL SELECT 'B', 'ME', 1
UNION ALL SELECT 'C', 'ME', 2
UNION ALL SELECT 'C', 'ME', 3
UNION ALL SELECT 'D', 'ME', 2
UNION ALL SELECT 'E', 'YOU', 2
UNION ALL SELECT 'F', 'YOU', 1
UNION ALL SELECT 'G', 'ME', 3
UNION ALL SELECT 'H', 'YOU', 2
UNION ALL SELECT 'H', 'YOU', 3
UNION ALL SELECT 'I', 'ME', 1) sq
GROUP BY Ticket, UserX
ORDER BY Ticket;
September 3, 2014 at 1:57 pm
Quick solution (if I got it right 😉
😎
USE tempdb;
GO
;WITH BASE_DATA(Ticket,[User],[Priority]) AS
(SELECT Ticket,[User],[Priority] FROM
( VALUES
('A','ME' , 1)
,('B','ME' , 1)
,('C','ME' , 2)
,('C','ME' , 3)
,('D','ME' , 2)
,('E','YOU', 2)
,('F','YOU', 1)
,('G','ME' , 3)
,('H','YOU', 2)
,('H','YOU', 3)
,('I','ME' , 1)
) AS X(Ticket,[User],[Priority])
)
SELECT
BD.Ticket
,BD.[User]
,MIN(BD.[Priority]) OVER
(
PARTITION BY BD.Ticket,[User]
) AS [Priority]
FROM BASE_DATA BD
Results
Ticket User Priority
------ ---- -----------
A ME 1
B ME 1
C ME 2
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 2
I ME 1
September 3, 2014 at 2:06 pm
The way I would do this is:-
--Create table
CREATE TABLE Ticket
([Ticket] CHAR(1),
[User] CHAR(3),
[Priority] TINYINT);
GO
--Insert data
INSERT INTO dbo.[Ticket]
SELECT 'A', 'ME', 1 UNION ALL
SELECT 'B', 'ME', 1 UNION ALL
SELECT 'C', 'ME', 2 UNION ALL
SELECT 'C', 'ME', 3 UNION ALL
SELECT 'D', 'ME', 2 UNION ALL
SELECT 'E', 'YOU', 2 UNION ALL
SELECT 'F', 'YOU', 1 UNION ALL
SELECT 'G', 'ME', 3 UNION ALL
SELECT 'H', 'YOU', 2 UNION ALL
SELECT 'H', 'YOU', 3 UNION ALL
SELECT 'I', 'ME', 1;
GO
--Query data
SELECT
T.[Ticket],
T.[User],
MIN(T.[Priority]) OVER (PARTITION BY T.[Ticket], T.[User]) AS [Priority]
FROM
dbo.[Ticket] T;
GO
September 3, 2014 at 2:13 pm
Worked great.
Added DISTINCT to remove the duplicates but this is what I was after.
Thanks.
September 3, 2014 at 10:47 pm
jamie_collins (9/3/2014)
Worked great.Added DISTINCT to remove the duplicates but this is what I was after.
Thanks.
An alternative is to use the ROW_NUMBER function to filter the output
😎
USE tempdb;
GO
;WITH BASE_DATA(Ticket,[User],[Priority]) AS
(SELECT Ticket,[User],[Priority] FROM
( VALUES
('A','ME' , 1)
,('B','ME' , 1)
,('C','ME' , 2)
,('C','ME' , 3)
,('D','ME' , 2)
,('E','YOU', 2)
,('F','YOU', 1)
,('G','ME' , 3)
,('H','YOU', 2)
,('H','YOU', 3)
,('I','ME' , 1)
) AS X(Ticket,[User],[Priority])
)
,FINAL_SET(Ticket,[User],[Priority],P_RID) AS
(
SELECT
BD.Ticket
,BD.[User]
,MIN(BD.[Priority]) OVER
(
PARTITION BY BD.Ticket,[User]
) AS [Priority]
,ROW_NUMBER() OVER
(
PARTITION BY BD.Ticket,[User]
ORDER BY (SELECT NULL)
) AS P_RID
FROM BASE_DATA BD
)
SELECT
FS.Ticket
,FS.[User]
,FS.[Priority]
FROM FINAL_SET FS
WHERE FS.P_RID = 1
Results
Ticket User Priority
------ ---- -----------
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply