Need Help to Optimize Query

1

I have a table named TransList with the following columns:

ID Int (Identity column)
TransNum varchar(10)
TransData1 varchar(50)
TransData2 varchar(50)
TransData3 varchar(50)

The TransNum is not unique and can be repeating the same value many times. Only the ID is unique. I would like to do a query that shows only the rows for the first occurrence of each TransNum. Here is what I did:

SELECT * FROM TransList T
WHERE ID = (SELECT MIN(ID) FROM TransList WHERE TransNum = T.TransNum)

I'm not sure if my query is efficient or not. Could you please comment and maybe suggest a better way that would run faster?

Thank you so much in advance for your much appreciated help! :slight_smile:

2

Sorry I posted this as uncategorized. Moderator, please delete this post. I have posted it again in the Transact SQL category.

Thanks! :slight_smile:

3 
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY TransNum ORDER BY ID) AS row_num
   FROM TransList T
) AS derived
WHERE row_num = 1
1 Like
4

Thank you! Your solution looks neat because the "derived" subquery is executed only once.

I was afraid the "SELECT MIN(ID) FROM TransList WHERE TransNum = T.TransNum" in the WHERE clause of my query would execute every time for each row of the table. Do you think so too?

5

Yes. The inner query would have to be re-run for every outer row, because the query references an outer row column, there's no other way for the optimizer to do it.

6

Thank you so much!