<p>I have a table containing prices for a lot of different "things" in a MS SQL 2005 table. There are hundreds of records per thing per day and the different things gets price updates at different times.</p>

<pre><code>ID uniqueidentifier not null,
ThingID int NOT NULL,
PriceDateTime datetime NOT NULL,
Price decimal(18,4) NOT NULL
</code></pre>

<p>I need to get today's latest prices for a group of things. The below query works but I'm getting hundreds of rows back and I have to loop trough them and only extract the latest one per ThingID. How can I (e.g. via a GROUP BY) say that I want the latest one per ThingID? Or will I have to use subqueries?</p>

<pre><code>SELECT * 
FROM Thing
WHERE ThingID IN (1,2,3,4,5,6)
  AND PriceDate &gt; cast( convert(varchar(20), getdate(), 106) as DateTime) 
</code></pre>

<p><strong>UPDATE:</strong> In an attempt to hide complexity I put the ID column in a an int. In real life it is GUID (and not the sequential kind). I have updated the table def above to use uniqueidentifier.</p>

[{'answer_id': 49414, 'author': 'BlaM', 'author_id': 999, 'author_profile': 'https://Stackoverflow.com/users/999', 'pm_score': 5, 'selected': True, 'text': '<p>I think the only solution with your table structure is to work with a subquery:</p>\n\n<pre><code>SELECT *\n   FROM Thing\n   WHERE ID IN (SELECT max(ID) FROM Thing \n                   WHERE ThingID IN (1,2,3,4)\n                   GROUP BY ThingID)\n</code></pre>\n\n<p>(Given the highest ID also means the newest price)</p>\n\n<p>However I suggest you add a "IsCurrent" column that is 0 if it\'s not the latest price or 1 if it is the latest. This will add the possible risk of inconsistent data, but it will speed up the whole process a lot when the table gets bigger (if it is in an index). Then all you need to do is to...</p>\n\n<pre><code>SELECT *\n   FROM Thing\n   WHERE ThingID IN (1,2,3,4)\n     AND IsCurrent = 1\n</code></pre>\n\n<p><strong>UPDATE</strong></p>\n\n<p>Okay, Markus updated the question to show that ID is a uniqueid, not an int. That makes writing the query even more complex.</p>\n\n<pre><code>SELECT T.* \n   FROM Thing T\n   JOIN (SELECT ThingID, max(PriceDateTime)\n            WHERE ThingID IN (1,2,3,4)\n            GROUP BY ThingID) X ON X.ThingID = T.ThingID \n                                AND X.PriceDateTime = T.PriceDateTime\n   WHERE ThingID IN (1,2,3,4)\n</code></pre>\n\n<p>I\'d really suggest using either a "IsCurrent" column or go with the other suggestion found in the answers and use "current price" table and a separate "price history" table (which would ultimately be the fastest, because it keeps the price table itself small).</p>\n\n<p>(I know that the ThingID at the bottom is redundant. Just try if it is faster with or without that "WHERE". Not sure which version will be faster after the optimizer did its work.)</p>\n'}, {'answer_id': 49424, 'author': 'Mark Nold', 'author_id': 4134, 'author_profile': 'https://Stackoverflow.com/users/4134', 'pm_score': 2, 'selected': False, 'text': "<p>I would try something like the following subquery and forget about changing your data structures.</p>\n\n<pre><code>SELECT\n *\nFROM\n Thing\nWHERE \n (ThingID, PriceDateTime) IN \n (SELECT \n   ThingID, \n   max(PriceDateTime ) \n  FROM \n   Thing \n  WHERE \n   ThingID IN (1,2,3,4)\n  GROUP BY \n   ThingID\n )\n</code></pre>\n\n<p><strong>Edit</strong> the above is ANSI SQL and i'm now guessing having more than one column in a subquery doesnt work for T SQL. Marius, I can't test the following but try;</p>\n\n<pre><code>SELECT\n p.*\nFROM\n Thing p,\n (SELECT ThingID, max(PriceDateTime ) FROM Thing WHERE ThingID IN (1,2,3,4) GROUP BY ThingID) m\nWHERE \n p.ThingId = m.ThingId\n and p.PriceDateTime = m.PriceDateTime\n</code></pre>\n\n<p>another option might be to change the date to a string and concatenate with the id so you have only one column. This would be slightly nasty though.</p>\n"}, {'answer_id': 49425, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': 1, 'selected': False, 'text': '<p>It depends on the nature of how your data will be used, but if the old price data will not be used nearly as regularly as the current price data, there may be an argument here for a price history table. This way, non-current data may be archived off to the price history table (probably by triggers) as the new prices come in.</p>\n\n<p>As I say, depending on your access model, this could be an option.</p>\n'}, {'answer_id': 49427, 'author': 'Nick Pierpoint', 'author_id': 4003, 'author_profile': 'https://Stackoverflow.com/users/4003', 'pm_score': 2, 'selected': False, 'text': '<p>If the subquery route was too slow I would look at treating your price updates as an audit log and maintaining a ThingPrice table - perhaps as a trigger on the price updates table:</p>\n\n<pre><code>ThingID int not null,\nUpdateID int not null,\nPriceDateTime datetime not null,\nPrice decimal(18,4) not null\n</code></pre>\n\n<p>The primary key would just be ThingID and "UpdateID" is the "ID" in your original table.</p>\n'}, {'answer_id': 49446, 'author': 'BirgerH', 'author_id': 5164, 'author_profile': 'https://Stackoverflow.com/users/5164', 'pm_score': 1, 'selected': False, 'text': "<p>I'm converting the uniqueidentifier to a binary so that I can get a MAX of it.\nThis should make sure that you won't get duplicates from multiple records with identical ThingIDs and PriceDateTimes:</p>\n\n<pre><code>SELECT * FROM Thing WHERE CONVERT(BINARY(16),Thing.ID) IN\n(\n SELECT MAX(CONVERT(BINARY(16),Thing.ID))\n  FROM Thing\n  INNER JOIN\n   (SELECT ThingID, MAX(PriceDateTime) LatestPriceDateTime FROM Thing\n    WHERE PriceDateTime &gt;= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)\n    GROUP BY ThingID) LatestPrices\n  ON Thing.ThingID = LatestPrices.ThingID\n   AND Thing.PriceDateTime = LatestPrices.LatestPriceDateTime\n GROUP BY Thing.ThingID, Thing.PriceDateTime\n) AND Thing.ThingID IN (1,2,3,4,5,6)\n</code></pre>\n"}, {'answer_id': 49527, 'author': 'Peter', 'author_id': 5189, 'author_profile': 'https://Stackoverflow.com/users/5189', 'pm_score': 1, 'selected': False, 'text': '<p>Since ID is not sequential, I assume you have a unique index on ThingID and PriceDateTime so only one price can be the most recent for a given item.</p>\n\n<p>This query will get all of the items in the list IF they were priced today.  If you remove the where clause for PriceDate you will get the latest price regardless of date.</p>\n\n<pre><code>SELECT * \nFROM Thing thi\nWHERE thi.ThingID IN (1,2,3,4,5,6)\n  AND thi.PriceDateTime =\n     (SELECT MAX(maxThi.PriceDateTime)\n      FROM Thing maxThi\n      WHERE maxThi.PriceDateTime &gt;= CAST( CONVERT(varchar(20), GETDATE(), 106) AS DateTime)\n        AND maxThi.ThingID = thi.ThingID)\n</code></pre>\n\n<p>Note that I changed ">" to ">=" since you could have a price right at the start of a day</p>\n'}, {'answer_id': 49528, 'author': 'Pop Catalin', 'author_id': 4685, 'author_profile': 'https://Stackoverflow.com/users/4685', 'pm_score': 2, 'selected': False, 'text': "<p>Since you are using SQL Server 2005, you can use the new (CROSS|OUTTER) APPLY clause. The APPLY clause let's you join a table with a table valued function.</p>\n\n<p>To solve the problem, first define a table valued function to retrieve the top n rows from Thing for a specific id, date ordered:</p>\n\n<pre><code>CREATE FUNCTION dbo.fn_GetTopThings(@ThingID AS GUID, @n AS INT)\n  RETURNS TABLE\nAS\nRETURN\n  SELECT TOP(@n) *\n  FROM Things\n  WHERE ThingID= @ThingID\n  ORDER BY PriceDateTime DESC\nGO\n</code></pre>\n\n<p>and then use the function to retrieve the top 1 records in a query:</p>\n\n<pre><code>SELECT *\n   FROM Thing t\nCROSS APPLY dbo.fn_GetTopThings(t.ThingID, 1)\nWHERE t.ThingID IN (1,2,3,4,5,6)\n</code></pre>\n\n<p>The magic here is done by the APPLY clause which <strong>applies the function to every row in the left result set</strong> then joins with the result set returned by the function then retuns the final result set. (Note: to do a left join like apply, use OUTTER APPLY which returns all rows from the left side, while CROSS APPLY returns only the rows that have a match in the right side)</p>\n\n<p>BlaM:\nBecause I can't post comments yet( due to low rept points) not even to my own answers ^^, I'll answer in the body of the message:\n   -the APPLY clause even, if it uses table valued functions it is optimized internally by SQL Server in such a way that it doesn't call the function for every row in the left result set, but instead takes the inner sql from the function and converts it into a join clause with the rest of the query, so the performance is equivalent or even better (if the plan is chosen right by sql server and further optimizations can be done) than the performance of a query using subqueries), and in my personal experience APPLY has no performance issues when the database is properly indexed and statistics are up to date (just like a normal query with subqueries behaves in such conditions)</p>\n"}, {'answer_id': 49543, 'author': 'MobyDX', 'author_id': 3923, 'author_profile': 'https://Stackoverflow.com/users/3923', 'pm_score': 0, 'selected': False, 'text': '<p>Try this (provided you only need the latest <em>price</em>, not the identifier or datetime of that price)</p>\n\n<pre><code>SELECT ThingID, (SELECT TOP 1 Price FROM Thing WHERE ThingID = T.ThingID ORDER BY PriceDateTime DESC) Price\nFROM Thing T\nWHERE ThingID IN (1,2,3,4) AND DATEDIFF(D, PriceDateTime, GETDATE()) = 0\nGROUP BY ThingID\n</code></pre>\n'}, {'answer_id': 17063932, 'author': 'Novalis', 'author_id': 2478038, 'author_profile': 'https://Stackoverflow.com/users/2478038', 'pm_score': -1, 'selected': False, 'text': '<p>maybe i missunderstood the taks but what about a: </p>\n\n<p><code>SELECT ID, ThingID, max(PriceDateTime), Price \n   FROM Thing GROUP BY ThingID</code></p>\n'}, {'answer_id': 17277562, 'author': '30thh', 'author_id': 608164, 'author_profile': 'https://Stackoverflow.com/users/608164', 'pm_score': 1, 'selected': False, 'text': '<p>It must work without using a global PK column (for complex primary keys for example):</p>\n\n<pre><code>SELECT t1.*, t2.PriceDateTime AS bigger FROM Prices t1 \nLEFT JOIN Prices t2 ON t1.ThingID = t2.ThingID AND t1.PriceDateTime &lt; t2.PriceDateTime \nHAVING t2.PriceDateTime IS NULL\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/49404', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1008/']