tag:blogger.com,1999:blog-4646137438366687895.post5635885806808200881..comments2024-03-24T02:48:23.930-07:00Comments on Demystifying SQL Server: Select The Most Current RowAdam Haineshttp://www.blogger.com/profile/16288608920551626835noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-4646137438366687895.post-65777887042778505542021-01-04T23:46:45.559-08:002021-01-04T23:46:45.559-08:00I guess one needs to always look ahead and search ...I guess one needs to always look ahead and search for the most happening aspect of SSIS which makes it able enough to provide large complex database operations.<br /><br /><a href="https://zappysys.com/products/SSIS-powerpack/SSIS-upsert-destination" rel="nofollow">SSIS Upsert</a><br /><br />James Zicrovhttps://www.blogger.com/profile/00094356083987556970noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-21921155733146993622015-11-16T09:18:38.162-08:002015-11-16T09:18:38.162-08:00Thank you so much for this post. You saved my day ...Thank you so much for this post. You saved my day at work! I was trying to figure out how to accomplish this with my client's data. <br /><br />I'm very grateful for the extensive explanation and great details!<br /><br />Cheers from Costa Rica!Anonymoushttps://www.blogger.com/profile/18431580691563986947noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-80926231611570998992010-12-09T12:59:21.421-08:002010-12-09T12:59:21.421-08:00I believe you have also heard about the exchange o...I believe you have also heard about the <a href="http://www.recoverytoolbox.com/exchange_server.html" rel="nofollow">exchange ost file recovery</a> tooldaspeachttps://www.blogger.com/profile/07882283802002193798noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-64340400769644386842009-09-17T04:57:48.109-07:002009-09-17T04:57:48.109-07:00Here are several examples from Uma:
http://social...Here are several examples from Uma:<br /><br />http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4b569f6a-de09-476f-8d3c-293d8d89aa4a/<br /><br />in this one it is Uma's last example:<br /><br />http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8bedb6c8-9bdb-49b9-8824-d332c3eb78a2/<br /><br />In this post, Denis Repin suggests something similar:<br /><br />http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/1409d8e6-257b-4096-9df4-d992895c65f5/Kent Waldrophttps://www.blogger.com/profile/11169211720676440554noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-6700560459567724002009-09-17T04:26:48.082-07:002009-09-17T04:26:48.082-07:00Do you prefer that I post here or email you? I wi...Do you prefer that I post here or email you? I will gather the information and check back.<br /><br />KentKent Waldrophttps://www.blogger.com/profile/11169211720676440554noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-47747715306548275102009-09-16T05:48:36.886-07:002009-09-16T05:48:36.886-07:00Kent,
Do you have an example of this other method...Kent,<br /><br />Do you have an example of this other method, or a link that may demonstrate it? I would love to see this method and how it works.Adam Haineshttps://www.blogger.com/profile/16288608920551626835noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-88206881079341172022009-09-16T05:24:30.203-07:002009-09-16T05:24:30.203-07:00There is also a "vector" method for doin...There is also a "vector" method for doing this kind of query that is usually more efficient thant the given method and works in all version of SQL Server from the current millenium; however, this method is anything but straight forward.<br /><br />Kent WaldropKent Waldrophttps://www.blogger.com/profile/11169211720676440554noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-79692285917796534292009-09-15T13:29:28.010-07:002009-09-15T13:29:28.010-07:00Unless the time is part of a natural key, i'd ...Unless the time is part of a natural key, i'd use a surrogate. Leaving data integrity up to common sense is a sure way to find out that it isn't so common. :)Brian Tkatchhttps://www.blogger.com/profile/11320700842381820277noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-36920362380708234072009-09-15T12:12:49.231-07:002009-09-15T12:12:49.231-07:00@Brian,
Good point :). In this scenario it is lik...@Brian,<br /><br />Good point :). In this scenario it is likely that data integrity can be smudged.<br /><br />My thoughts are that no person should ever have the same exact timestamp on an order, regardless of backloading data. A person is only able to make a single order in a millisecond's time, so no other record should have this exact timestamp. If you are transfering data from another system that has a lesser precise data type, this is definitely possible. I think we are back to the precision of the data type, knowing our data and maintaining the integrity of our data.<br /><br />Thanks for the comment.Adam Haineshttps://www.blogger.com/profile/16288608920551626835noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-42121527807213144652009-09-15T11:16:31.375-07:002009-09-15T11:16:31.375-07:00@Adam
At the same time can happen when loading a...@Adam<br /><br /> At the same time can happen when loading a history from elsewhere. Or back- or forward- dating.<br /><br />Later, people forget these things.<br /><br />Just some thought.Brian Tkatchhttps://www.blogger.com/profile/11320700842381820277noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-33940668725316816292009-09-15T10:12:11.658-07:002009-09-15T10:12:11.658-07:00Thanks for the input Brad.
I agree that the SQL...Thanks for the input Brad. <br /><br />I agree that the SQL 2000 method could have some problems, although unlikely. It comes back to knowing your data. I could have used the identity column to get the most current record, for each customer. Using the surrogate key eliminates any tie issues, but I wanted to choose a column that more closely reflected the data I am trying to return. <br /><br />I would hope that no customer can place multiple orders at the exact ms, but anything is possible :). If this scenario does occur you can use a surrogate key instead of the date or you can create logic to eliminate ties.<br /><br />Thanks for sharing your thoughts!!Adam Haineshttps://www.blogger.com/profile/16288608920551626835noreply@blogger.comtag:blogger.com,1999:blog-4646137438366687895.post-21336494794885974332009-09-15T09:46:18.483-07:002009-09-15T09:46:18.483-07:00Hi Adam...
Great post as always.
I would suggest...Hi Adam...<br /><br />Great post as always.<br /><br />I would suggest that the SQL2000 query could be rewritten as below. It creates the exact same query plan as your query, but I think it's a little more readable as to what's going on:<br /><br />SELECT whatever<br />FROM Customers c<br />JOIN Orders o ON c.CustomerID=o.CustomerID<br />WHERE Order_Dt=<br />(SELECT MAX(Order_Dt)<br />FROM Orders o2<br />WHERE o2.CustomerID=o.CustomerID)<br /><br />Unfortunately another problem with the SQL2000 method is that it can return multiple entries for the same customer. If you INSERTed another row into the Orders table...<br /><br />INSERT INTO Orders<br />VALUES (3,4.00,'2009-09'08');<br /><br />...and execute the query again, you'd get two entries for CustomerID 3.<br /><br />You'd have to introduce a tiebreaker of some kind like this ugly method:<br /><br />SELECT whatever<br />FROM Customers c<br />JOIN Orders o ON c.CustomerID=o.CustomerID<br />WHERE CONVERT(varchar,Order_Dt,126)+STR(OrderID)=<br />(SELECT MAX(CONVERT(varchar,Order_Dt,126)+STR(OrderID))<br />FROM Orders o2<br />WHERE o2.CustomerID=o.CustomerID)<br /><br />Also, here's a really clever out-of-the-box way (SQL2005 only) to also get the most recent order information that I saw posted on the T-SQL Forum:<br /><br />SELECT TOP 1 WITH TIES Whatever<br />FROM Customers c<br />JOIN Orders o on c.CustomerID=o.CustomerID<br />ORDER BY<br />RANK() OVER <br />(PARTITION BY c.CustomerID<br />ORDER BY o.Order_Dt desc, o.OrderID desc)<br /><br />--BradBrad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.com