Doubt in a query

Hello DBAs,

I am stuck in a query and need your help with that. I have a table with 40 columns. Out of those columns two are [Instance ID] and [DATE]. [Instance ID] may be duplicated in the table. My requirement is to fetch only those rows with distinct [Instance ID] with the maximum date for each [Instance ID]. Note that I want all the 40 columns in the result set. Also, there are 12 rows where the combination of {[Instance ID], [DATE]} is also duplicated. I also want to remove these rows. For example

 Instance ID        DATE
  1              2
  1              3
  1              4
  2              1
  2              3
  2              3
  3              5
  3              6

The result set should look like:

 Instance ID       DATE
  1              4
  2              3
  3              6

Any help is much appreciated.

more ▼

asked Nov 15, 2011 at 10:38 AM in Default

avatar image

31 3 3 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
 ;with cte
 Select *, row_number() over(partition by instanceid order by date desc) as rn from tablename
 Select * from cte where rn=1
more ▼

answered Nov 15, 2011 at 10:43 AM

avatar image

Håkan Winther
16.5k 36 45 57

(comments are locked)
10|1200 characters needed characters left

I am using this query but not getting the correct results. Can you tell whats wrong?

(SELECT [Instance ID],MAX([Date]) AS D FROM MyTableName
GROUP BY [Instance ID]) AS M 
ON A.[Instance ID] = M.[Instance ID] 
WHERE A.[Date] = M.D

more ▼

answered Nov 15, 2011 at 10:46 AM

avatar image

31 3 3 5

Maybe you should use a left join and move the where clause into the join

Nov 15, 2011 at 10:53 AM Håkan Winther

@Nikhil With your version it is not possible to get correct results in case if there are records with the same date for the same id. For example, using the rows from your question, there are 2 records for instance 2 with the same date, so your query will result in 4 records instead of desired 3. This means that @Håkan Winther's answer is the best way of dealing with this situation because it guarantees to pick only one record per instance id. Since you have other columns in your actual table, it means that SQL Server will still pick just one record in the scenario when there are multiple records for the same id with the same date which also happens to be the biggest date, but which one of the 2 will be picked is hard to tell. The odds are that the one with the smaller value in the clustered column will be picked but then again, there is no guarantee.

Nov 15, 2011 at 12:27 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 15, 2011 at 10:38 AM

Seen: 1193 times

Last Updated: Nov 15, 2011 at 10:38 AM

Copyright 2016 Redgate Software. Privacy Policy