1

In SQL Server 2016, say I have a simple table defined as:

CREATE TABLE MyTable (
    ID int NOT NULL,
    Timestamp timestamp,
    Category varchar(255),
    Value decimal(10,2),
    PRIMARY KEY (ID) 
);

I am trying to find the latest value of every unique category. Currently I am using the following query multiple times:

SELECT TOP (1) Category, Value FROM MyTable WHERE Category =
'WhateverCategory1' ORDER BY Timestamp DESC

This works, however the categories need to be hardcoded in the query. Also, there are multiple result sets, one for each category. How would I find the latest value for every unique category, all in the same result set?

Andrew Drake
  • 655
  • 1
  • 11
  • 25
  • What version of SQL Server? USE `ROW_NUMBER()` window function. – Shawn Nov 29 '18 at 22:06
  • 1
    are you using sql server or mysql? You mixed a mysql datatype with top 1 of sql server – S3S Nov 29 '18 at 22:09
  • This is for SQL Server 2016 – Andrew Drake Nov 29 '18 at 22:34
  • 1
    I'm not sure that the linked question is relevant to this one. First, one of the things I dislike about marking dupes is the changes in languages from 7 years ago. Second, how this was accomplished in SQL 2005/2008 probably doesn't apply to more modern versions of MS SQL, though I'm not sure this _is_ T-SQL. Third, Gordon's answer is much better than the older accepted answer. – Shawn Nov 29 '18 at 22:35
  • And I correct myself. Use the correlated subquery example that Gordon spoke about in his answer. – Shawn Nov 29 '18 at 22:37
  • 1
    And also see my note about `timestamp` data type being deprecated. It's a bad name for what it is and it shouldn't be used in newer dbs. – Shawn Nov 29 '18 at 22:39
  • Thanks for the advice @Shawn, this table was just an example, but in my real table the timestamp is coming from an OPC server.. long story. Perhaps I should just store that as another data type and select the "top" row through something else. Like in Elaskanator's example, MAX(ID) – Andrew Drake Nov 29 '18 at 22:45
  • As far as I know, `timestamp` isn't a value you can insert. It's a value that the SQL Server itself stores. I would definitely change the datatype, since `timestamp` is deprecated. Use `rowversion`. And you can't store `max(id)` because you don't know what it is until you query the rows. `timestamp` datatype doesn't really have anything to do with a time. It's a binary number that MS SQL essentially uses for row versioning. – Shawn Nov 30 '18 at 12:15

1 Answers1

4

Assuming you're using Timestamp as the row creation time, just get it by max ID.

Here is a solution not using a window function:

SELECT Y.*
FROM
    (
        SELECT MaxID = MAX(ID)
        FROM MyTable
        GROUP BY Category
    ) AS X
    INNER JOIN MyTable AS Y ON
        Y.ID = X.MaxID
Elaskanator
  • 1,135
  • 10
  • 28
  • If it's actually SQL Server, then `timestamp` datatype will be unique by its nature, but it's deprecated in favor of `rowversion`. If it _IS_ SQL Server, I'd still use the window function. But I suspect this is MySQL, which unless Version 8+, won't have window functions, so this method would work. – Shawn Nov 29 '18 at 22:23
  • Somehow I misread the `Timestamp` column's datatype. Facepalm @ self. Thanks. – Elaskanator Nov 29 '18 at 22:25