Doubt in a query

Question

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.

Answer 1

0

;with cte
As
(
Select *, row_number() over(partition by instanceid order by date desc) as rn from tablename
)
Select * from cte where rn=1

answered Nov 15 '11 at 10:43 AM

Håkan Winther
15k ● 29 ● 35 ● 46

(comments are locked)

Answer 2

0

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

answered Nov 15 '11 at 10:46 AM

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)

Doubt in a query

Follow this question

Related Questions