x

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 '11 at 10:38 AM in Default

Nikhil gravatar image

Nikhil
31 3 3 5

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

2 answers: sort voted first
;with cte
As
(
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 '11 at 10:43 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(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 * FROM MyTableName A JOIN (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 '11 at 10:46 AM

Nikhil gravatar image

Nikhil
31 3 3 5

Maybe you should use a left join and move the where clause into the join
Nov 15 '11 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 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933
x1816
x265

asked: Nov 15 '11 at 10:38 AM

Seen: 875 times

Last Updated: Nov 15 '11 at 10:38 AM