TSQL query

Base on the TSQL below, what's the purpose of adding "MAX"  ? Can anyone think of any possible reason ? Does it mean it may return such records on PIC3, PIC2 and PIC1 and we use 'MAX' to restrict to one record ?

select inventbatchid,itemid,a2,a3,a4,
Tent,
max(case typeid when 'ImgS' then pic else '' end) as 'PIC3',
max(case typeid when 'sketchA' then pic else '' end) as 'PIC2',
max(case typeid when 'sketchB' then pic else '' end) as 'PIC1'  
into #tmp3 from #tmp2
AXISHKAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes. in MySQL, the GROUP BY is not mandatory to get the MAX to work...
but I would recommend to add it up to the statement to make it clearer.

you may want to read up this article around that concept:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
PortletPaulConnect With a Mentor Commented:
Is there a GROUP BY in the source code?

One suggestion is that MAX() is being used to ensure only one value is returned, but without sample data there's a lot of guesswork involved.
0
 
PortletPaulCommented:
I would go further about MySQL's default slackness on GROUP BY!
By default there is an "extension" which in my opinion leads to lazy code that can produce poor results.

see:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_28500137.html#a40270222
and
http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by

However turning off the "extension" may affect existing scripts.
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
it is just to get the not null value

Max(pic,'','','') >>> pic

case returns null for non 'ImgS' records, and returns pic for 'ImgS' record (s)

max(case typeid when 'ImgS' then pic else '' end) as 'PIC3',

maybe it is really getting max of those records like

Max(123,234,102,'','','',327,'') >>>327, just dealing with 'ImgS' records...
0
All Courses

From novice to tech pro — start learning today.