Matt B Matt B - 5 months ago 20
SQL Question

SQL Max() Returning multiple values

Good morning/afternoon & thank you for checking on this, I'm struggling to figure out what I'm missing.

Attempting to put together a schedule system off the results (separate task) but because our schedules change several times a day/week its creating too many duplicates.

Select modified, local_Start_Time, 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 modified
,local_start_time
,local_end_time
,seg_code
,date
,employee


When I run this, I get this Table as expected.

I tried adding max () around the 'modified' select to limit it to the latest entry, but that did not change anything from the above. I then added

max(Local_START_Time)
,max(Local_END_time)
,max(modified)


to the selects, which did limit it to 1 line per employee however its now taking the opposite entries from what it should per this [table 2][2]

I tried adjusting it from max to min on the start/end times and it fixed some and broke others.

I'm fairly new to SQL and have tried looking at other questions/videos but to no avail. I understand the logic where it's taking the 'max' modified as the most recent time entry, and then max (highest starting / end time) from those 2 cells but in a perfect world I'd prefer only one max value (modified) and then it pairs that to the start & end times.

Can anyone guide me in the right direction with what I'm missing to make this work either with 1 max statement, or if using all 3 / a different one entirely?

Using SQL Management Studio & apologies if I'm missing any other key details, thank you for your help and looking forward to learning more & helping others!

Edit To clarify the challenge I'm having, Updated Image, cells in green are both the latest updated schedule per date whereas the cells in red are what is being captured by the max(start/end time).

Hope that helps

Answer

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= '###'
    ;
Comments