CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15787

Return most recent data

I have a database table, which is structured like this:

CREATE table match (ID1 int, ID2 int, CreatedDate, ConnectDisconnect)

I am trying to write an SQL statement that will return the most recent rematch record grouped by id1 and id2. For example, please see the data below:

1,2,'2014-06-05', C
1,3,'2014-06-05', C
1,4,'2014-06-05', C
N1,N2,'2014-06-05',D

Please see the SQL statement below:

select max(CreatedDate), ID1,ID2 FROM match
group by ID1,ID2

This will show the most recent decision that was made on ID1 and ID2. The problem is that the matching records can be either way around. For example:

1,2,'2014-06-04', C
2,1,'2014-06-05', D

The data above shows that records 1 and 2 were connected on 04/06/2014 and disconnected on 05/06/2014. My query above will return two rows, however I only want it to return one row i.e. the most recent (the data dated 05/06/14 in the case above).

Upvotes: 0

Views: 76

Answers (2)

Kevin Postlewaite
Kevin Postlewaite

Reputation: 615

It's ugly but this may do what you want (not sure what the syntax of concatenation is in SQL Server):

select max(CreatedDate), case when ID1 < ID2 concat(ID1,',',ID2) else concat(ID2,',',ID1) end as combinedIds
FROM match
group by case when ID1 < ID2 concat(ID1,',',ID2) else concat(ID2,',',ID1) end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Here is one approach, using case and row_number():

select m.*
from (select m.*,
             row_number() over (partition by (case when id1 < id2 then id1 else id2 end),
                                             (case when id1 < id2 then id2 else id1 end)
                                order by CreatedDate desc
                               ) as seqnum
      from match m
     ) m
where seqnum = 1;

Upvotes: 1

Related Questions