Sql Max Function Returns One Row When Multiple Rows Have the Same Value

Why MAX always returns a row?

What you are seeing is called aggregation. MAX is an aggregation function, as are MIN, SUM, COUNT, AVG, etc. Aggregation functions lead to exactly one result row, unless you specify a GROUP BY clause or a HAVING clause.

A GROUP BY results in one or more rows, as it says "Give me an aggegation result per ___", e.g. "Give me the number of employees and the highest salary per department" which would result in one row per department.

The HAVING clause is like a WHERE clause on the aggragation result. In the given example you could add "but only for departments with at least ten employees" for instance.

Hence, if you don't want a result row in case there is no value in col (i.e. there exists no maximum value), add a HAVING clause:

SELECT MAX(col) FROM my_table HAVING MAX(col) IS NOT NULL;

Here is the SQL Server documentation on aggregation functions: https://docs.microsoft.com/de-de/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15

EDIT:

As to whether there exist means to return nulls instead of no row in case of no data matching the WHERE clause: The WHERE clause eliminates the rows from the result, so we must find ways to get around this. One is outer joins, another is unioned queries and there may be other methods, too.

An example:

select dept_id, dept_name from departments where boss = 'Mr. X';

would return no row, if there isn't any department where the boss is Mr. X.

But we get null rows when outer joining tables:

select d.dept_id, d.dept_name 
from (select 1) dummy
left outer join departments d on boss = 'Mr. X';

Or we combine two queries, one for the match and one for no match:

select dept_id, dept_name from departments where boss = 'Mr. X'
union all
select null, null where not exists (select * from departments where boss = 'Mr. X');

How to select single row based on the max value in multiple rows

The way I try to solve SQL problems is to take things step by step.

• You want the maximum revision for the maximum minor version corresponding to the maximum major version for each product.

The maximum major number for each product is given by:

SELECT Name, MAX(major) AS Major FROM CA GROUP BY Name;

The maximum minor number corresponding to the maximum major number for each product is therefore given by:

SELECT CA.Name, CA.Major, MAX(CA.Minor) AS Minor
  FROM CA
  JOIN (SELECT Name, MAX(Major) AS Major
          FROM CA
         GROUP BY Name
       ) AS CB
    ON CA.Name = CB.Name AND CA.Major = CB.Major
 GROUP BY CA.Name, CA.Major;

And the maximum revision (for the maximum minor version number corresponding to the maximum major number for each product), therefore, is given by:

SELECT CA.Name, CA.Major, CA.Minor, MAX(CA.Revision) AS Revision
  FROM CA
  JOIN (SELECT CA.Name, CA.Major, MAX(CA.Minor) AS Minor
          FROM CA
          JOIN (SELECT Name, MAX(Major) AS Major
                  FROM CA
                 GROUP BY Name
               ) AS CB
            ON CA.Name = CB.Name AND CA.Major = CB.Major
         GROUP BY CA.Name, CA.Major
       ) AS CC
    ON CA.Name = CC.Name AND CA.Major = CC.Major AND CA.Minor = CC.Minor
 GROUP BY CA.Name, CA.Major, CA.Minor;

Tested - it works and produces the same answer as Andomar's query does.

---

Performance

I created a bigger volume of data (11616 rows of data), and ran a benchmark timing of Andomar's query against mine - target DBMS was IBM Informix Dynamic Server (IDS) version 11.70.FC2 running on MacOS X 10.7.2. I used the first of Andomar's two queries since IDS does not support the comparison notation in the second one. I loaded the data, updated statistics, and ran the queries both with mine followed by Andomar's and with Andomar's followed by mine. I also recorded the basic costs reported by the IDS optimizer. The result data from both queries were the same (so the queries are both accurate - or equally inaccurate).

Table unindexed:

Andomar's query                           Jonathan's query
Time: 22.074129                           Time: 0.085803
Estimated Cost: 2468070                   Estimated Cost: 22673
Estimated # of Rows Returned: 5808        Estimated # of Rows Returned: 132
Temporary Files Required For: Order By    Temporary Files Required For: Group By

Table with unique index on (name, major, minor, revision):

Andomar's query                           Jonathan's query
Time: 0.768309                            Time: 0.060380
Estimated Cost: 31754                     Estimated Cost: 2329
Estimated # of Rows Returned: 5808        Estimated # of Rows Returned: 139
                                          Temporary Files Required For: Group By

As you can seen, the index dramatically improves the performance of Andomar's query, but it still seems to be more expensive on this system than my query. The index gives a 25% time saving for my query. I'd be curious to see comparable figures for the two versions of Andomar's query on comparable volumes of data, with and without the index. (My test data can be supplied if you need it; there were 132 products - the 3 listed in the question and 129 new ones; each new product had (the same) 90 version entries.)

The reason for the discrepancy is that the sub-query in Andomar's query is a correlated sub-query, which is a relatively expensive process (dramatically so when the index is missing).

Return the row includes the maximum value of specific column if two rows have the same values.

The question, sample data, and desired results are lacking a bit.

But if I understand your question, you can use the WITH TIES clause in concert with Row_Number()

Example

Select Top 1 with ties *
 From  YourTable
 Order By Row_Number() over (Partition By YourCol1 Order By YourLastCol Desc)

Edit Use Dense_Rank() if you want to see ties

SQL select multiple max rows where ID is same

Query:

SQLFIDDLEExample

SELECT t1.*
FROM Table1 t1
WHERE t1.Date = (SELECT MAX(t2.Date)
                 FROM Table1 t2
                 WHERE t2.AccountID = t1.AccountID)

Result:

| ROW |                            DATE |    ACCOUNTID |
--------------------------------------------------------
|   1 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   2 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   3 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   7 | February, 12 2009 12:00:00+0000 | A023123332YP |

Return more than one row when using MAX SQL

this does not uses dense_rank(), should works on MySQL also

SELECT t.*
FROM   yourtable t
INNER JOIN
(
  SELECT PositionId, max(VoteCount) as VoteCount
  FROM   yourtable
  GROUP BY PositionId
) m    ON t.PositionId = m.PositionId
       AND t.VoteCount = m.VoteCount

How to use MAX() for multiple occurrences of Max values in SQL

MAX() is a scalar function returns a single value and not records so if you have multiple records that have the same value which is maximum, the following will still return only one value:

SELECT MAX(Value) FROM MyTable

If you want to get all records that have the maximum value, you can use

SELECT * FROM MyTable
WHERE Value = (SELECT MAX(Value) FROM MyTable)

SQL MAX join query returns multiple rows

Rows in a relational database have no order. There's no "last" entry unless something specifies an order.

In this case it appears the order is by ascending inventory_id_inventory for each inventory_id_inventory. Given that order, one can use the ROW_NUMBER() ranking function to calculate a rank for each result of the join , eg :

SELECT 
  isni.id,
  isni.inventory_id_inventory,
  isni.issue_note_id_issue_notes,
  isn.direction,
  ROW_NUMBER() OVER(partition by inventory_id_inventory 
                    order by issue_note_id_issue_notes desc) as rn
FROM issue_note_items isni
JOIN issue_notes isn ON isni.issue_note_id_issue_notes = isn.issue_note_id

partition by inventory_id_inventory means we want a separate ranking per partition by inventory_id_inventory. order by issue_note_id_issue_notes desc will return 1 for the largest ID, making it easier to filter for the "latest" entry.

It's not possible to refer to the rn function in the where clause. This can be fixed by using a CTE :

with notes as (
    SELECT 
      isni.id,
      isni.inventory_id_inventory,
      isni.issue_note_id_issue_notes,
      isn.direction,
      ROW_NUMBER() OVER( partition by inventory_id_inventory 
                         order by issue_note_id_issue_notes desc) as rn
    FROM issue_note_items isni
    JOIN issue_notes isn ON isni.issue_note_id_issue_notes = isn.issue_note_id      
)
select * 
from notes
where rn=1
order by issue_note_id_issue_notes

Using the following data :

create table issue_notes (issue_note_id int,direction int);
create table issue_note_items (id int, inventory_id_inventory  int,issue_note_id_issue_notes int);

insert into issue_notes
values
(1,1),
(2,2),
(3,2),
(4,1),
(5,2),
(6,1);

insert into issue_note_items 
values
(1,12 ,1),
(2,123,1),
(3,12 ,2),
(4,12 ,4),
(5,35 ,4),
(6,123,5),
(7,35 ,6);

The result will be :

|id |inventory_id_inventory |issue_note_id_issue_notes  |direction  |rn
|4  |12                     |4                          |1          |1
|6  |123                    |5                          |2          |1
|7  |35                     |6                          |1          |1

SQL Fiddle here

Check multiple rows for value, return only row with MAX/MIN

Well if you only want the newest row you could use the following:

SELECT TOP 1 key, name, value, date
FROM myTable
ORDER BY date desc

This should return the one row with the newest date in that table.

If you wanted the newest date for each name you could use group by:

SELECT name, max(date)
FROM myTable
WHERE key in(1,2,3,4)
GROUP BY name

Max is an aggregate function. Anytime you use an aggregate function any columns that are not being aggregated have to be specified in the group by clause.

So based on your expected results you probably want this:

;with namesWithMaxDate as(
 select
   name
  ,max(date) as date
 from
   myTable 
 group by
   name 
 )
 select 
   myTable.[key]
  ,myTable.name
  ,myTable.value
  ,myTable.date
 from myTable
 inner join
 namesWithMaxDate 
 on 
   myTable.name = namesWithMaxDate.name and 
   myTable.date = namesWithMaxDate.date

This is slightly more complex because you have columns that you want returned that are not included in the grouping. Hence two statements to arrive at the final result set.

Final option: good old fashioned sub-query.

 select 
   myTable.[key]
  ,myTable.name
  ,myTable.value
  ,myTable.date
 from myTable
  inner join
 (   select
       name
      ,max(date) as date
     from
       myTable 
     group by
       name ) as namesWithMaxDate
 on 
   myTable.name = namesWithMaxDate.name and 
   myTable.date = namesWithMaxDate.date

More here about aggregate functions.
More here about group by.

---

Related Topics