CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
cSharpDotNetGuy
cSharpDotNetGuy

Reputation: 175

Get the latest row of a table based on Latest Date and a distinct column in SQL Server 2008 R2

If I have the following SQL Table

<table border="1">
<tr><td>colOne</td><td>colTwo</td><td>colThree</td><td>colDate</td></tr>
<tr><td>0</td><td>1</td><td>8</td><td>2014-02-05 00:00:00.000</td></tr>
<tr><td>0</td><td>6</td><td>8</td><td>2014-05-13 00:00:00.000</td></tr>
<tr><td>0</td><td>10</td><td>37</td><td>2014-07-25 00:00:00.000</td></tr>
<tr><td>0</td><td>12</td><td>184</td><td>2014-08-11 00:00:00.000</td></tr>
<tr><td>0</td><td>13</td><td>11</td><td>2014-08-12 00:00:00.000</td></tr>
<tr><td>0</td><td>41</td><td>14</td><td>2015-02-16 00:00:00.000</td></tr>
<tr><td>0</td><td>42</td><td>14</td><td>2015-02-16 00:00:00.000</td></tr>
</table>

And I want the following result, which is the latest record of each distinct colThree

<table border="1">
<tr><td>colOne</td><td>colTwo</td><td>colThree</td><td>colDate</td></tr>
<tr><td>0</td><td>6</td><td>8</td><td>2014-05-13 00:00:00.000</td></tr>
<tr><td>0</td><td>10</td><td>37</td><td>2014-07-25 00:00:00.000</td></tr>
<tr><td>0</td><td>12</td><td>184</td><td>2014-08-11 00:00:00.000</td></tr>
<tr><td>0</td><td>13</td><td>11</td><td>2014-08-12 00:00:00.000</td></tr>
<tr><td>0</td><td>42</td><td>14</td><td>2015-02-16 00:00:00.000</td></tr>
</table>

Thanks a lot.

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Perhaps the fastest way is :

select t.*
from t
where t.colDate = (select max(t2.colDate) from t t2 where t2.colThree = t.colThree);

The more canonical way using row_number() will do what you want:

select t.*
from (select t.*,
             row_number() over (partition by colThree order by colDate desc) as seqnum
      from t
     ) t
where seqnum = 1;

In the event of ties, this will choose an arbitrary row.

Upvotes: 1

Related Questions