Sql-server – How to best handle MAX within a query/view

azure-sql-databasesql server

I have two tables named "Visit" and "VisitMovement" and one Visit can contain many VisitMovements.

Within my query I need to join to the latest record for the Visit in VisitMovement so I can get back the latest VisitMovement details.

To do this I have a view like this that I join to:

SELECT
    VisitID,
    max(VisitMovementID) as VisitMovementID
FROM
    dbo.VisitMovement
GROUP BY
    VisitID

and finish up with:

    SELECT 
        ......
        FROM 
            Visit V
        INNER JOIN VisitMovement VM ON
            V.VisitID = VM.VisitID
        INNER JOIN vwLatestVisitMovement LVM on
            VM.VisitMovementID = LVM.VisitMovementID

I can't Index this View due to the max operator, and I can see over-time this becoming a performance issue with the Index Scan operation.

I wondered if anyone had any good ideas on how to approach this? I was thinking of having a trigger and maintaining the latest VisitMovementID in the Visit table but this doesn't sit well with me.

Best Answer

I think (not very clear) that you want something either:

For a VIEW solution - use something like this

SELECT v.visitor_id AS vid, v.visitor_name
FROM visitor v
INNER JOIN vw_max_visit_movement vmw ON
v.visitor_id = vmw.visitor_id;

Personally, I might want to use a CTE (unless I had millions of visitors), as per here. The reason I mentioned that you might want to avoid this approach for large tables is best explained here.

WITH visitor_max AS
(
  SELECT visitor_id, MAX(visit_movement_id)
  FROM visit_movement
  GROUP BY visitor_id
)
SELECT v.visitor_id AS "Vistor No.", v.visitor_name AS "Visitor name"
FROM visitor v
INNER JOIN visitor_max vmax ON
v.visitor_id = vmax.visitor_id;

In any case, the results are the same (not sure if this is what you're asking - if not, please expand the question)

Vistor No.  Visitor name
         2        Paulie
         3           Jim
         4          Bill
         5          Fred

=== DDL and DML for the tables used =====

CREATE TABLE visit_movement
(
  visit_movement_id INTEGER,   -- possibly a timestamp?
  visitor_id INTEGER
);


INSERT INTO visit_movement 
VALUES
(34, 2),
(35, 2),
(34, 2),
(37, 3),
(38, 3),
(39, 3),
(40, 3),
(41, 4),
(42, 4),
(43, 5);


CREATE TABLE visitor
(
  visitor_id INTEGER,
  visitor_name VARCHAR (20)
);

INSERT INTO visitor
VALUES
(1, 'Mary'),
(2, 'Paulie'),
(3, 'Jim'),
(4, 'Bill'),
(5, 'Fred'),
(6, 'Xavier');

CREATE VIEW vw_max_visit_movement AS
SELECT visitor_id, MAX(visit_movement_id)
FROM visit_movement
GROUP BY visitor_id;
Related Question