SQL Server Max Statement Returns Multiple Results

Why does max function in sql return multiple values

You did't need MAX nor GROUP BY, just use TOP 1 with ORDER BY Salary DESC. Something like this:

select TOP (1) Salary as highest_salary, p.[Last name]
from tbl_PlayersTable as p, tbl_team as t
where p.Team = t.TeamID
 and TeamID = 1000
ORDER BY Salary  DESC

SQL SELECT MAX() is returning multiple records

Well, your results are unique, I suspect you want to get latest ID2 depending o date.

You can make use of ROW_NUMBER() in this case.

SELECT *
FROM (  SELECT ROW_NUMBER() OVER (PARTITION BY ID, NAME, SPECIAL_NUMBER, DATE_TIME ORDER BY ID2 DESC) AS RN
          , ID
          , NAME
          , ID2
          , SPECIAL_NUMBER
          , DATE_TIME
        FROM TABLE1
        WHERE DATE_TIME BETWEEN @START_DATE and @END_DATE) AS T
WHERE T.RN = 1;

SQL Max() Returning multiple values

If you want the max of the value you should perform the query without the group by you have setted for the column in aggregation function (max)

Select max(modified), max(local_Start_Time), max(local_End_Time), seg_code, Date, employee

from schedule_table

Where employee= '###' and seg_code = ‘schedule’
and Date in ('20160606', '20160607', '20160608', '20160609') 

group by seg_code
,date
,employee 

for getting the row related to max(modified) you should instead use a subquery

    Select modified, local_Start_Time, local_End_Time, seg_code, Date, employee
    from schedule_table
    where modified = (  select max(modified)
                         from schedule_table
                         Where employee= '###' and seg_code = ‘schedule’
                        and Date in ('20160606', '20160607', '20160608', '20160609') 
    );

And for get only the employee then

    Select modified, local_Start_Time, local_End_Time, seg_code, Date, employee
    from schedule_table
    where modified = (  select max(modified)
                         from schedule_table
                         Where employee= '###' and seg_code = ‘schedule’
                        and Date in ('20160606', '20160607', '20160608', '20160609') 
    )
    and employee= '###'
    ;

MAX function returning multiple values for each group

Can anyone explain what I am doing wrong?

There is nothing in your query that specifies the condition to limiting to the account with the highest free overdraft limit.

Since you tagged your question "Oracle", I'll assume you are using a relatively recent version and walk through how to do this using the CROSS APPLY join feature.

Since you want your result set to show one row per branch, start with just that table...

SELECT b.branchAddress.streetAddress, 
       b.branchAddress.city, 
       b.branchAddress.postcode, 
       b.branchID
FROM   kt_branchTable b

Next, use CROSS APPLY to find the ONE account having the highest overdraft limit, using the accountNumber to break ties.
(NOTE: Assumes every branch has at least one account. If not a good assumption, use OUTER APPLY/)

SELECT a.freeOverdraftLimit, 
       b.branchAddress.streetAddress, 
       b.branchAddress.city, 
       b.branchAddress.postcode, 
       b.branchID
FROM   kt_branchTable b
CROSS APPLY ( SELECT a.freeOverdraftLimit, a.accountNum
              FROM   kt_accountTable a
              WHERE  a.branchId = b.branchID
              ORDER BY a.freeOverdraftLimit DESC, a.accountNumber ASC
              FETCH FIRST 1 ROW ONLY ) a

From here, it's easy to add in the customer info for that account, giving you your final answer as follows:

SELECT a.freeOverdraftLimit, 
       b.branchAddress.streetAddress, 
       b.branchAddress.city, 
       b.branchAddress.postcode, 
       b.branchID, 
       c.pname.forename, 
       c.pname.surname
FROM   kt_branchTable b
CROSS APPLY ( SELECT a.freeOverdraftLimit, a.accountNum
              FROM   kt_accountTable a
              WHERE  a.branchId = b.branchID
              ORDER BY a.freeOverdraftLimit DESC
              FETCH FIRST 1 ROW ONLY ) a
INNER JOIN kt_customerAccount c ON c.accountNum = a.accountNum

NOTE: it's not clear from your data model whether multiple customers can own the same account (i.e., two rows in kt_customerAccount having the same accountNumber. If so, this solution will give duplicates. But you did not specify the requirements for handling that situation (show all, show first, etc.), so I'm not handling it as part of my answer here.

Returning multiple records whilst using a MAX function?

You just need to grab your max date with a subquery in your select list:

SELECT
    (SELECT Max(ticket_finish) from ticket) as cutOff, 
    ticket_price, 
    ticket_id, 
    ticket_name, 
    ticket_qty, 
    ticket_start, 
    ticket_finish, 
    ticket_max, 
    ticket_type_no, 
    ticket_min,
    ticket_order, 
    ticket_fee
FROM ticket
WHERE 
    ticket_event_no = :id
    AND ticket_hide = 0

That will pull the max value of ticket_finish and populate it as cutOff in all rows.

Why do I get multiple results from a MAX SQL query?

In MsAccess, NULL values appear as blanks when viewed on the Datasheet view. Also, it seems that when blanks are entered for a ShortText column on the Datasheet view, they are turned into NULLs (on my version of MsAccess). It seems that your data may have NULL for one of the Steps (say 200), and spaces (zero or more) for the other (say Step 190), you could force your query to treat both the same way:

SELECT Max(tblBundle.Step) AS intLstep, tblBundle.BundleNbr,
 nz(tblBundle.BundleLtr,'') as BundleLtr,
 tblBundle.Complete
FROM tblBundle
GROUP BY tblBundle.WO, tblBundle.BundleNbr,
         nz(tblBundle.BundleLtr,''), tblBundle.Complete
HAVING (((tblBundle.WO)="195687-1-1") AND ((tblBundle.Complete)=True));

Please note that the invisible character can, in fact, be a character that looks like space, but it is not (like non-breaking space), etc. If the above solution does not work, try something like:

SELECT  asc(mid(BundleLtr, 1,1)), asc(mid(BundleLtr, 2,1)) FROM Table2 where Step=190

To see the ascii values of the data in that column.

SQL - MAX returns multiple results when querying more than one column

You're grouping by LogoutDateTime. Just remove that.

Use MicrosoftDynamicsAX

SELECT  MAX(LOGOUTDATETIME) as LastLoggedOut,
        RIGHT(USERID,3) as UserName
FROM SYSUSERLOG

GROUP BY USERID

ORDER BY USERID

SQL max() over(partition) returning multiple results

I was taking the max of mt.version and partitioning it by mt.version so I wasn't getting any new data. A correct version of what I previously wrote could be:

select *, max(mt.version) over (partition by mt.partitiongroup)
from mytable mt
where mt.id = 'some uuid';

I wasn't realizing while trying out this new concept that the partition group had to be different than the max aggregation.

---

Related Topics