SQL query minimum maximum

Question

I need to get maximum and minimum values but also I need to get row id of these maximum or minimum on the same row.

SELECT MIN([Value]), MAX([Value]), id 
FROM [AnalystEstimates].[dbo].[AnalystEstimateValues] 
GROUP BY indicatorid

Answer 1

Your query would return the max/min for each ID as you are grouping by the ID. Try something like this

SELECT tblFoo.ID, tblFoo.Value
FROM tblFoo
WHERE (((tblFoo.Value)=(SELECT MAX( [tblFoo]![Value] ) FROM tblFoo))) 
   OR (((tblFoo.Value)=(SELECT MIN( [tblFoo]![Value] ) FROM tblFoo)));

Answer 2

SELECT TOP 1
    ID, 
    'min' as type, 
    value 
FROM 
    AnalystEstimateValues 
WHERE 
   value = (select min(value) from AnalystEstimateValues)
UNION
   SELECT TOP 1
    ID, 
    'max' as type, 
    value 
FROM 
    AnalystEstimateValues 
WHERE 
   value = (select max(value ) from AnalystEstimateValues)

Answer 3

If Min = Max, you'll the get same IDs (there may be tied Min and Max values)

If not, there is/are ID(s) per min/max value.

You can have 2 rows or 4 columns, basically.

SELECT
    Mn.ID, foo.MinVal,
    Mx.ID, foo.MaxVal     
FROM
    (
    SELECT
        MIN([Value]) AS MinVal,
        MAX([Value]) AS MaxVal,
        indicatorid
    FROM
        [AnalystEstimates].[dbo].[AnalystEstimateValues] 
    GROUP BY
        indicatorid
    ) foo
    JOIN
    [AnalystEstimates].[dbo].[AnalystEstimateValues] Mn ON
            foo.MinVal = Mn.[Value] AND foo.indicatorid = Mn.indicatorid
    JOIN
    [AnalystEstimates].[dbo].[AnalystEstimateValues] Mx ON
            foo.MaxVal = Mx.[Value] AND foo.indicatorid = Mx.indicatorid

Edit:

The TOP solutions won't give you rows where you have tied MIN/MAX values unless defined WITH TIES

Answer 4

This might do it, though I don't have mssql so I can't test it. Especially the square brackets may need tweaking. Other than that it should be fairly standard sql and does what you want.

It gets all min (id/value) and max (id/value) grouped by indicatorid. In the same row.

SELECT mint.indicatorid, mint.min_id, mint.min_value, maxt.max_id, maxt.max_value
FROM (
    SELECT indicatorid, id as min_id, MIN([Value]) AS min_value
    FROM [AnalystEstimates].[dbo].[AnalystEstimateValues]
    HAVING [Value] = min_value
    GROUP BY indicatorid
) mint JOIN (
    SELECT indicatorid, id as max_id, MAX([Value]) AS max_value
    FROM [AnalystEstimates].[dbo].[AnalystEstimateValues]
    HAVING [Value] = max_value
    GROUP BY indicatorid
) maxt ON mint.indicatorid = maxt.indicatorid

Answer 5

It's very unclear what you want from your question. Do you really want the GROUP BY indicatorid? If not then it's quite simple and you already have many answers. But if you do want to GROUP BY then it's more difficult and no-one has got it quite right yet. I also assume that you only want one row per indicatorid, and if there are duplicate rows that have the same max/min then it's better to just choose one of them arbitrarily instead of returning both.

Here's my attempt, using CTEs (requires SQL Server 2005 or newer):

WITH
    RowNumbers AS (
        SELECT ROW_NUMBER() OVER (ORDER BY indicatorid, value) AS RowNumber, *
        FROM [AnalystEstimates].[dbo].[AnalystEstimateValues]),
    MinRowNumbers AS (
        SELECT indicatorid, MIN(RowNumber) AS RowNumber FROM RowNumbers GROUP BY indicatorid),
    MaxRowNumbers AS (
        SELECT indicatorid, MAX(RowNumber) AS RowNumber FROM RowNumbers GROUP BY indicatorid)
SELECT
    MinRowNumbers.indicatorid,
    RN1.Value AS MinValue,
    RN1.ID AS MinValueId,
    RN2.Value AS MaxValue,
    RN2.ID AS MaxValueId
FROM MinRowNumbers
JOIN MaxRowNumbers ON MinRowNumbers.indicatorid = MaxRowNumbers.indicatorid
JOIN RowNumbers RN1 ON MinRowNumbers.RowNumber = RN1.RowNumber
JOIN RowNumbers RN2 ON MaxRowNumbers.RowNumber = RN2.RowNumber

Here is some data I used to test it:

CREATE TABLE AnalystEstimateValues (ID int, indicatorid int, Value int);

INSERT INTO AnalystEstimateValues (ID, indicatorid , Value) VALUES
(1, 1, 4),
(2, 1, 4),
(3, 2, 6),
(4, 1, 2),
(5, 2, 2),
(6, 2, 5),
(7, 3, 0);

And here's the output I get:

indicatorid MinValue MinValueId MaxValue MaxValueId
          1        2          4        4          2
          2        2          5        6          3
          3        0          7        0          7

If this isn't what you want, can you please try to improve your question to tell us what you do want?

---

Update: Here's an alternative solution based on Craig Young's answer but using joins instead of subselects:

WITH
    UniqueIds AS (
        SELECT IndicatorId, Value, MIN(id) AS Id
        FROM AnalystEstimateValues
        GROUP BY IndicatorId, Value)
SELECT
    lims.IndicatorId,
    MinValue,
    T1.Id AS MinValueId,
    MaxValue,
    T2.Id AS MaxValueId 
FROM (
        SELECT
            IndicatorId,
            MIN(Value) as MinValue,
            MAX(Value) as MaxValue
        FROM AnalystEstimateValues
        GROUP BY IndicatorId) lims
JOIN UniqueIds T1 ON lims.IndicatorId = T1.IndicatorId AND lims.MinValue = T1.Value
JOIN UniqueIds T2 ON lims.IndicatorId = T2.IndicatorId AND lims.MaxValue = T2.Value

This is cleaner and probably also faster than my first version, although I haven't run performance tests to verify this.

Answer 6

Important question
Mark Byers' sample data shows a scenario that you need to contemplate:

• IndicatorId=1 has a max value of 4.
• There are two Id's that share the same max value (1 & 2).

So which of the two id's should be displayed?

I have assumed it sufficient to display the lowest id. The following query should be the most efficient, and would benefit from an index on (indicatorid, Value).

SELECT  lims.*,
        (
        SELECT  MIN(id)
        FROM    AnalystEstimateValues m
        WHERE   m.IndicatorId = lims.IndicatorId
            AND m.Value = lims.MinValue
        ) AS MinId,
        (
        SELECT  MIN(id)
        FROM    AnalystEstimateValues m
        WHERE   m.IndicatorId = lims.IndicatorId
            AND m.Value = lims.MaxValue
        ) AS MaxId
FROM    (
        SELECT  IndicatorId,
                MIN(Value) as MinValue,
                MAX(Value) as MaxValue
        FROM    AnalystEstimateValues
        GROUP BY IndicatorId
        ) lims