CODEWITHSUNDEEP
t-sql
uzay95
uzay95

Reputation: 16632

T-Sql, How to get these results?

This query 

      SELECT PA.refPatient_id
            ,MAX(PA.datee) AS datee
            ,PR.temporary,PA.statue

        FROM PatientClinicActs AS PA
            ,PatientStatueReasons AS PR

       WHERE PA.refClinic_id = 25 
         AND PA.refreason_id = PR.reason_id

    GROUP BY PA.refPatient_id,PA.statue,PR.temporary

returns these results:

refPatient_id datee                 temporary statue
----------- ----------------------- ------ -----
14706       2008-12-01 11:01:00     1      0
14707       2009-05-18 16:21:00     1      0
14708       2009-07-15 09:46:00     1      0
14708       2009-07-29 16:12:00     1      0
14716       2009-11-09 12:29:00     0      0
14716       2009-09-01 11:15:00     1      0
14716       2009-09-29 16:44:00     1      1

But i want to have these results:

refPatient_id datee                   temporary statue
----------- ----------------------- ------ -----
14706       2008-12-01 11:01:00     1      0
14707       2009-05-18 16:21:00     1      0
14708       2009-07-29 16:12:00     1      0
14716       2009-11-09 12:29:00     0      0

What is the difference ? => I have newest row for every refPatient_id in these results. What should i run to get these results?

Upvotes: 1

Views: 206

Answers (4)

dance2die
dance2die

Reputation: 36925

For this kind of cases, there is another way to get the latest record by using SQL Server Ranking functions.

I have used DENSE_RANK() for my answer, but you can use RANK(), instead for your particular problem.
(note: code below is not tested. If you were to provide schema for tables, I would have tested with sample data)

;with RankedResult as (
    SELECT  
    PA.refPatient_id
    , PA.datee
    , PR.temporary,
    , PA.statue
    --; Last datee has the lowest rank value of 1,
    , dense_rank() over 
        (partition by PA.refPatient_id order by PA.datee desc) as [Rank]
    FROM    PatientClinicActs AS PA
            join PatientStatueReasons AS PR on PA.refreason_id = PR.reason_id
    WHERE   PA.refClinic_id = 25 
)
select  distinct *
from    RankedResult
--; Get only the last result.
where   [Rank] = 1

Upvotes: 0

priyanka.sarkar
priyanka.sarkar

Reputation: 26518

Instead of a cross join 

FROM PatientClinicActs AS PA
                ,PatientStatueReasons AS PR

can you try with inner join

FROM PatientClinicActs AS PA

INNER JOIN PatientStatueReasons AS PR 

ON PA.refreason_id = PR.reason_id

WHERE PA.refClinic_id = 25

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166476

Try something like this

SELECT  PA.refPatient_id,
        PA.datee,
        PR.temporary,
        PA.statue
FROM    PatientClinicActs AS PA INNER JOIN
        (
            SELECT  PA.refPatient_id,
                    MAX(PA.datee) AS datee
            FROM    PatientClinicActs AS PA
            WHERE   PA.refClinic_id = 25          
            GROUP BY PA.refPatient_id,PA.statue,PA.datee,
        ) AS MaxDates ON PA.refPatient_id = MaxDates.refPatient_id AND PA.datee = MaxDates.datee INNER JOIN
        PatientStatueReasons AS PR ON PA.refreason_id = PR.reason_id

You need to get the max date per patient first, then join to that.

Upvotes: 3

Sparky
Sparky

Reputation: 15105

Take the PA.datee out of the group by clause

Upvotes: -2

Related Questions