Selecting the record with the maximum value on one of the connections
I have three related tables and need to select rows that show data from two tables based on the value (serial number) from the third. I am only interested in the maximum value of the serial number. I tried several solutions suggested here on stackoverflow and I still can't get around it.
Sample code for my tables with direct SELECT for all values is available here: http://sqlfiddle.com/#!6/6b8f7/4/0
My ultimate goal is to get a table like this:
reference groupname serialnum
C:123 Group2 3
C:125 Group1 4
C:126 Group1 1
Ordering with LIMIT doesn't work. Any ideas how this could be solved?
DDL + DML for example data:
CREATE TABLE pm_process
([pm_guid] int, [Descr] varchar(4), [usr_newref] varchar(5))
;
INSERT INTO pm_process
([pm_guid], [Descr], [usr_newref])
VALUES
(11111, 'aaaa', 'C:123'),
(22222, 'bbbb', 'C:125'),
(33333, 'cccc', 'C:126')
;
CREATE TABLE tps_group
([tps_title] varchar(6), [tps_guid] int)
;
INSERT INTO tps_group
([tps_title], [tps_guid])
VALUES
('Group1', 99999),
('Group2', 88888)
;
CREATE TABLE pm_process_assignment
([pm_group_guid] int, [pm_process_guid] int, [pm_serial_number] int)
;
INSERT INTO pm_process_assignment
([pm_group_guid], [pm_process_guid], [pm_serial_number])
VALUES
(99999, 11111, 1),
(99999, 11111, 2),
(88888, 11111, 3),
(88888, 22222, 1),
(99999, 22222, 2),
(88888, 22222, 3),
(99999, 22222, 4),
(99999, 33333, 1)
;
source to share
In SQL Server, perhaps the easiest way to do this is APPLY
:
SELECT p.usr_newref as reference,
pag.tps_title as groupname,
pag.pm_serial_number as serialnum
FROM pm_process p OUTER APPLY
(SELECT TOP 1 pa.pm_serial_number, g.tps_title
FROM pm_process_assignment pa JOIN
tps_group g
ON g.tps_guid = pa.pm_group_guid
WHERE pa.pm_process_guid = p.pm_guid
ORDER BY pm_serial_number DESC
) pag
Here is the SQL script.
source to share
You can use ROW_NUMBER()
to search for records with the maximum serialnum
in each section reference
. Then, in the outer query, select only these entries:
SELECT reference, groupname, serialnum
FROM (
SELECT
pm_process.usr_newref as reference,
pm_assignment_group.tps_title as groupname,
process_assignments.pm_serial_number as serialnum,
ROW_NUMBER() OVER (PARTITION BY pm_process.usr_newref
ORDER BY process_assignments.pm_serial_number DESC) AS rn
FROM
tps_group pm_assignment_group
RIGHT OUTER JOIN pm_process_assignment process_assignments
ON (pm_assignment_group.tps_guid=process_assignments.pm_group_guid)
RIGHT OUTER JOIN pm_process
ON (process_assignments.pm_process_guid=pm_process.pm_guid)
) t
WHERE t.rn = 1
source to share
Use RANK
AND CTE
. It will handle the situation where multiple pm_serial_number have the same value.
WITH RankTable AS
(
SELECT *, RANK() OVER (
PARTITION BY usr_newref
ORDER BY pm_serial_number DESC) AS R
FROM pm_process
LEFT JOIN pm_process_assignment
ON pm_process_assignment.pm_process_guid = pm_process.pm_guid
LEFT JOIN tps_group
ON tps_group.tps_guid = pm_process_assignment.pm_group_guid
)
SELECT usr_newref, tps_title, pm_serial_number
FROM RankTable
WHERE RankTable.R = 1
source to share