question

aRookieBIdev avatar image
aRookieBIdev asked

SQL SELECT QUERY HELP

Hi All, I have a table as below TableA (Code,Description,effectivedate) ![alt text][1] We have identified a list of possible duplicates , in the above case there are 2 Codes with the same description .However I need to take only "ALB". The code that shall be picked is marked with (B) in the identified code list and these identified codes could be hard coded in the query and it is not available in any table. The next rule is quite simple ; when there are duplicate codes with different description chose the latest code based on effective date. ![alt text][2] I need to get the data from the table based on 2 rules. Please suggest me some ideas. Thanks in advance. NK [1]: /storage/temp/1544-1.png [2]: /storage/temp/1545-2.png
sqlsql 2012
1.png (18.6 KiB)
2.png (19.5 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
I'd suggest using a TOP 1 in a sub-query to identify the appropriate version of the values you want. I have an article on how to do this [over on Simple-Talk][1]. You're not versioning the data except by date, but that will work the same way. [1]: https://www.simple-talk.com/sql/database-administration/sql-strategies-for-versioned-data/
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.