Selecting a record with max value on one of the joins

Question

I have three related tables and need to select rows that show data from two tables based on a value (serial number) from the third. I am only interested in the max value of the serial number. I have tried multiple solutions suggested here on stackoverflow and I still cannot get my head around this.

A sample code for my tables with a straight forward SELECT for all values are available here: http://sqlfiddle.com/#!6/6b8f7/4/0

My end goal is to obtain a table like this:

reference   groupname   serialnum
C:123       Group2      3
C:125       Group1      4
C:126       Group1      1

Ordering with LIMIT does not seem to work. Any ideas how this might be addressed?

DDL + DML for Sample 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)
;

RIGHT JOIN... Few humans understand those. Save the world and use LEFT JOIN instead. — jarlh, May 13 '15 at 11:54
@jarlh: It's only a matter of perspective. you can look at the screen through a mirror of that helps :-) — Zohar Peled, May 13 '15 at 12:03
@Pawzik: Linking to sql fiddle is excellent, It may save a lot of time and effort from whoever wants to answer your question. However, you should include the DDL and DML in your message as well, since sql fiddle suffers from a lot of downtime. — Zohar Peled, May 13 '15 at 12:05
possible dup of http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category?rq=1 — qxg, May 13 '15 at 12:09

score 4 · Accepted Answer · edited May 13 '15 at 12:39

4

In SQL Server, probably the easiest way to do this is using 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 Fiddle.

edited May 13 '15 at 12:39

Zohar Peled

79,642
10
69
121

answered May 13 '15 at 12:01

Gordon Linoff

1,242,037
58
646
786

1

@Gordon: I've added the link to the relevant technet page. hope you don't mind... – Zohar Peled May 13 '15 at 12:39

score 3 · Answer 2 · answered May 13 '15 at 12:00

You can use ROW_NUMBER() to locate records having the maximum serialnum within each reference partition. Then, in an outer query, select only these records:

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

SQL Fiddle Demo

This also gives me what I wanted. Thank you! – Pawzik May 13 '15 at 12:25 — Pawzik, May 13 '15 at 12:25

score 0 · Answer 3 · answered May 13 '15 at 12:05

Use RANK AND CTE. It will handle situation when multiple pm_serial_number has 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

Selecting a record with max value on one of the joins

3 Answers3