little confusion with the max keyword
-
whats the difference between these two statements:
select customerNum ,max(customerName) from #Drilldown_tblCustomer
group by customerNum
select customerNum ,customerName from #Drilldown_tblCustomer
select max(customerName) from #Drilldown_tblCustomer
group by customerNum
the first returns many rows, as does the 2nd , but the 3rd only returns 1. i know the 3rd returns the customer name it deems the largest. the second just returns all.
how come the 1st returns more than 1 row??
-
On your first query you are asking SQL for each CustomerNumber and the max(CustomerName) for that CustomerNumber - I guess each CustomerNumber probably only has one CustomerName so you get all customers.
Allen
-
Because of the group by. What you're asking for in the first statement is the maximum customerName for every unique customerNum. Hence, you'll get one row for every customerNum
The third should return the same number of rows as the 1st. Not sure why it returned 1. Should only return 1 if there is only 1 customerNum in the entire table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ist & 3rd should definately return the same number of rows - there is no join or where and the group by is the same.
-
When I ran this query in QA on one of my DBs. I got the same number of rows each time. Of course the third version gave me one less column.
Not that this helps any
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply