CODEWITHSUNDEEP
sqlsql-server-2008-r2
Yasskier
Yasskier

Reputation: 811

Select latest from joined table excluding duplicates

I have two joined tables, parent one shows unit's name, child shows recording temperatures, that can be inserted either by automatic process (AUTO) or by user. So for given unit reading records from simple join would look like

UNIT      TEMP       TIMESTAMP      DATA_SOURCE
ABC       -20        10:26          AUTO
ABC       -19        11:27          USER
ABC       -19        11:27          AUTO

The goal is to select the latest temp reading. I can use subquery to do so:

   SELECT A.UNIT, B.TEMP, B.TIMESTAMP,B.DATA_SOURCE
   FROM units_table A left outer join readings_table B on A.Gkey=B.unit_gkey
   WHERE B.TIMESTAMP= 
           (SELECT MAX(TIMESTAMP) FROM readings_table B1 
            WHERE A.Gkey=B1.unit_gkey)

It would be simple but in the example above there are two exact timestamps, so I will get TWO readings. In such case I'd like to ignore the AUTO source. Is there an elegant way to do it?

Edit: to be clear I want only ONE ROW result: ABC -19 11:27 USER

Upvotes: 1

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can do this with row_number() instead:

SELECT ut.UNIT, rt.TEMP, rt.TIMESTAMP, rt.DATA_SOURCE
FROM units_table ut left outer join
     (SELECT rt.*,
             row_number() over (partition by rt.unit_Gkey
                                order by timestamp desc,
                                         (case when rt.data_source = 'AUTO' then 1 else 0 end)
                               ) as seqnm
      FROM readings_table rt
     ) rt
     on rt.unit_Gkey = ut.gkey
WHERE rt.seqnum = 1;

Note: if you wanted the duplicates, you would use rank() or dense_rank() instead of row_number() (and remove the second clause in the order by).

Upvotes: 2

user3718713
user3718713

Reputation: 21

http://www.w3schools.com/sql/sql_distinct.asp

just use the distinct key word look at the example! :)

Upvotes: 0

Related Questions