Can Select max(col_name) Return > 1 Row? 1

[Coder7]

Good afternoon everyone.

Can/will the sql statement:

Select max(start_date)where customer = 'ABC'

return >1 row if there are multiple rows in the table that contain the highest date value?

i.e. table data

customer start_date

ABC 2002-06-04
ABC 2001-11-04
ABC 2002-06-04

Thanks for any help.

 

[PruSQLer]

No, it will just return the maximum start date in a 1 row result.

 

[Coder7]

Thanks for getting back to me. That's what I thought. I'm trying to debug an sql statement that got a -811 in Production. I'll post some details later. I THINK I know what might have caused the -811 but I'm not sure and would appreciate some expert opinions.

 

[Coder7]

Hi again.

Okay..here's the code that I think might cause a -811. What do you think? Thanks for your insight!

---------------
table data:

table1:
cust_no chg_eff_date end_reason_code
16552 1997-11-01 abc
16552 1997-11-01 def
16552 2004-01-04 abc


---------------
the query:

select distinct end_reason_code
from table1
where cust_no = 16552
and chg_eff_date =
(select max(chg_eff_date)
from table1 a
where a.cust_nbr = 16552)

 

[PruSQLer]

Well, one problem is that the subquery is calling it cust_nbr and the base query is calling it cust_no.

 

[BrianTyler]

Selecting DISTINCT will retrieve any number of rows, but duplicates will be eliminated. In your example with the sub-query, you would get multiple rows returned if the third row was not present so that two rows had the latest date, one for reason 'abc' and one for reason 'def'.

If this code is in a program, you would probably be better to create a cursor or use the OLAP rownumber functions to select only the first row

 

[Coder7]

Thanks for the feedback.

<--Pru: I just a made up and example but thx for pointing out the column name inconsistency

<--Brian: I am fixing a query written by someone else that got a -811 in production which caused the job to abend. I have a fix but I'm trying to identify what duplicate rows were returned to ensure that I'm correctly identifying the problem. For the 'quick fix' I changed the table value of one of the chg-eff-dates so that no duplicate values for chg_eff_date existed in the table and restarted the job and it ran fine eoj. Based on that I'd have to say that two rows having the same chg_eff_date caused the -811 but I need to understand why/when/how a 'select max(whatever)...' can return > 1 row. My thought was it was due to the select distinct on end_reason_code since the value of end_reason_code was different on the two rows that had the same chg_eff_date.

Thanks again for taking the time to reply and help.