asked on

Query syntax

Hello,

I have a table of Persons:

Table Persons(
id : int,
Name : nvarchar(100) ;
unit : int ;
);

and a table of Measurements:

Table Measurements(
id: int,
pat_id ; int,
mDate : date,
nDate : date,
unit : integer );

Th link between the two tables is Measurements.Pat_id ->> Persons.Id

I need to create a query that returns a row for each person in the Persons table, with the details of the latest row information in the Measurements table. This is given by mDate.

So match each Person with the row in the Measurements table that belongs to the person and has the latest date value in mDate.

Can anyone help?

Vitor Montalvão

Use the MAX() function to return the last date and group it by Name to have the latest date per person:

SELECT P.Name, MAX(M.Date)
FROM Persons P 
    INNER JOIN Measurements M ON M.Pat_id = P.Id 
GROUP BY P.Name

Open in new window

soozh

ASKER

THanks victor.

That would work if i only wanted the mDate value, but i also want that values of id (from measurements) and nDate from the row with the maximum mDate.

There can be many rows for each person, and there is no garantee that the record with Max(mDate) is also the record with Max(id) and Max(nDate).

/richard

Vitor Montalvão

Try this version then:

;WITH CTE_MaxDate AS
(
	SELECT Pat_id, MAX(Date) MaxDate
	FROM Measurements
	GROUP BY Pat_id
)
SELECT P.Name, M.id, M.nDate, C.MaxDate
FROM Measurements M  
	INNER JOIN CTE_MaxDate C ON M.Pat_id = C.Pat_id AND M.Date = C.MaxDate
   INNER JOIN Persons P ON M.Pat_id = P.Id

Open in new window

ste5an

Just a comment:

Check you semantics. Persons are not necessarily patients. Thus

Th link between the two tables is Measurements.Pat_id ->> Persons.Id

is imprecise.

Either it should be Measurements.Pers_id -> Persons.Id or Measurements.Pat_id --> (Patients.Pat_ID, Patients.Pers_ID) -> Persons.Id.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

soozh

ASKER

@Steffan - i agree with your comment about semantics... but sometime we have to live with the mistakes of those who came before us.

And thanks for the solution.

Vitor Montalvão

soozh, if you have this issue solved please close this question by accepting the comment or comments that helped you.
Cheers

soozh

ASKER

Hej Vitor - sorry i though i already had when i added my last comment. But now i cant see how to close the question. How do i do that ?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Thanks for the points soozh but if you want to mark a comment as solution that really has the information that helped you to solve the issue and not to close this question :)
Cheers