asked
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.
;with cte As ( Select *, row_number() over(partition by instanceid order by date desc) as rn from tablename ) Select * from cte where rn=1
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
@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.
Maybe you should use a left join and move the where clause into the join
