Need help trying to find MAX Value in column

  • Good evening,

     

    I'm trying to find the MAX value on the TOC column, my current query does show all the values within the date range. What I just want to achieve is to just display a row with the MAX value of the TOC and the date. Note: I'm doing this query into an INSQL server.

    Any help would be greatly appreciated! Thanks in advance.

    Here's the current query:

    SELECT DateTime, 
    MAX(CASE WHEN TagName = 'WFI_LP01_TOC.PV' THEN value ELSE '' END) AS TOC

    FROM runtime.dbo.History
    WHERE TagName IN ('WFI_LP01_TOC.PV')
    AND DateTime >= '2018-01-01'
    AND DateTime <= '2018-01-08'
    AND wwRetrievalMode = 'Cyclic'
    GROUP BY DateTime
    Attachments:
    You must be logged in to view attached files.
  • So you want the TOC column to show the same value for all the rows within the selected range?

    • This reply was modified 3 years, 11 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Greetings,

    No, I just want a single row with the highest TOC column value in the selected range.

  • My first comment on this is with the TagName appearing in the WHERE clause, you likely don't need to worry about the CASE statement in the MAX.

    Looking at the result image and reading your description and query, I am not sure what is missing.  Could you provide sample output of what you expect to come out of this query?

    If you are meaning that you want the max value for the DATE and to exclude the time, one way to do this would be to use a CTE (or nested select) on your data to convert the DATETIME value DateTime to a DATE value.  My opinion, this would be the easiest way assuming I am understanding your requirement correctly.  Something like:

    WITH cte AS (
    SELECT CAST([DateTime] AS DATE) AS [Date],
    [Value] AS [Value]
    FROM [runtime].[dbo].[History]
    WHERE [TagName] IN ('WFI_LP01_TOC.PV')
    AND [DateTime] >= '2018-01-01'
    AND [DateTime] <= '2018-01-08'
    AND [wwRetrievalMode] = 'Cyclic'
    )
    SELECT [Date],
    MAX([Value]) AS [TOC]
    FROM cte
    GROUP BY [Date]

    NOTE - above code is untested as I do not have data to test it with.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • SELECT TOP (1) DateTime, 
    TOC = value
    FROM runtime.dbo.History
    WHERE TagName = 'WFI_LP01_TOC.PV'
    AND DateTime >= '2018-01-01'
    AND DateTime <= '2018-01-08'
    AND wwRetrievalMode = 'Cyclic'
    ORDER BY value desc

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks a lot! This is exactly what I was looking for. Thanks for your time

  • Sorry if I wasn't clear. I was looking for a query that shows only a single row with the MAX TOC value in the selected range. This code by Phil did the trick:

    SELECT TOP (1) DateTime, 
    TOC = value
    FROM runtime.dbo.History
    WHERE TagName = 'WFI_LP01_TOC.PV'
    AND DateTime >= '2018-01-01'
    AND DateTime <= '2018-01-08'
    AND wwRetrievalMode = 'Cyclic'
    ORDER BY value desc

     

    Thanks for your time!

    • This reply was modified 3 years, 11 months ago by  tripari.
    Attachments:
    You must be logged in to view attached files.
  • What is the data type of "value"?

    It appears from your results that it might be numeric.  But in your first attempt you use

    value ELSE ''

    in the CASE.

    It appears that you don't need that CASE, since you're filtering on TagName.

    But if "value" is a character string, your max may not come up with what you are expecting.  For example, compare the max of

    10.99 and 8.88

    vs the max of

    "10.99" and "8.88"

    You would get different results.

     

  • Hi,

    Correct, data type is float. I found the answer with Phil, which don't use the CASE that I was using. Thanks for your time!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply