Geek Answers Handbook

Minimum SQL query maximum

I need to get the maximum and minimum values, but also I need to get the row id from these highs or lows on the same line.

SELECT MIN([Value]), MAX([Value]), id FROM [AnalystEstimates].[dbo].[AnalystEstimateValues] GROUP BY indicatorid
+1
source share
6 answers

It is not clear what you want from your question. Are you sure you want the GROUP BY indicator? If not, it’s pretty simple, and you already have many answers. But if you want GROUP BY, then this is more complicated, and no one got it right. I also assume that you only need one line per indicator, and if there are duplicate lines having the same max / min, then it’s better to just select one of them arbitrarily and not return both.

Here is my attempt using CTE (requires SQL Server 2005 or later):

 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 are some of the data I used for testing:

 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 is the result that I get:

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

If this is not what you want, can you try to improve your question to tell us what you want?

Update: here's an alternative solution based on Craig Young that answers, but uses joins instead of subqueries:

 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

It is cleaner and probably faster than my first version, although I did not run performance tests to verify this.

+3
source

If Min = Max, you will get the same identifiers (Min and Max values ​​can be bound)

If not, there is / - ID (s) for the minimum / maximum value.

Basically you can have 2 rows or 4 columns.

 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:

In TOP solutions, you won’t see the rows where you bound MIN / MAX values ​​if they are not defined WITH TIES

+1
source

Important question
The Mark Bayer sample data shows a scenario that you need to consider:

  • IndicatorId = 1 has a maximum value of 4.
  • There are two identifiers that have the same maximum value (1 and 2).

So which of the two identifiers should be displayed?

I suggested that displaying the lowest id is enough. The next query should be the most effective, and the index (indicator, value) will be useful to it.

 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
+1
source

The request will return a maximum / min for each identifier that you group by identifier. 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)));
0
source 
 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)
0
source

This can do it, although I don't have mssql, so I can't test it. Especially square brackets may need customization. In addition, it should be pretty standard sql and does what you want.

It gets all min (id / value) and max (id / value), grouped by indicator. In the same line.

 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
0
source

More articles:


All Articles