Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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
0
AXISHK
Asked:
AXISHK
  • 2
3 Solutions
 
PortletPaulCommented:
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
 
Guy Hengel [angelIII / a3]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
 
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
 
HainKurtSr. 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now