CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Pawzik
Pawzik

Reputation: 78

Selecting a record with max value on one of the joins

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)
;

Upvotes: 3

Views: 169

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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.

Upvotes: 4

qxg
qxg

Reputation: 7036

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

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 3

Related Questions