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

x
?
Solved

MAX function

Posted on 2014-12-15
7
Medium Priority
?
97 Views
Last Modified: 2014-12-16
suppose the records in a table containing the records below :

inventbatchid       itemid     a2      a3       a4      PIC3            
PO10901                 AB0001    A       DX      20       c:\photo\abc135.jpg
PO10901                 AB0001    A       DX      20       c:\photo\abc13512321321.jpg
PO10901                 AB0001    A       DX      20       c:\photo\ac135.jpg

In this case, which record will be returned by "MAX" function ? Tks

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
Comment
Question by:AXISHK
  • 3
  • 3
7 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40501934
without typeid in the data we cannot answer

ie. the case expressions require a value in the field typeid, that value is not provided in your data
0
 

Author Comment

by:AXISHK
ID: 40501974
TypeID will be either 'IMgS', 'sketchA' and 'sketchB'. In the example above, typeID is 'ImgS'.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40501978
which record will be returned by "MAX" function ?
c:\photo\ac135.jpg
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:AXISHK
ID: 40501992
What's the selection criteria ?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 40502399
The same as text sorting. The c after a is the "biggest" in that location of the 3 strings, so that makes this the maximum
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 40503025
Presumably you'd want each max based on a specific inventbatchid, something like this:


select t2.inventbatchid,t2.itemid,t2.a2,t2.a3,t2.a4,t2.Tent,
    t2_pics.PIC3,t2_pics.PIC2,t2_pics.PIC1
into #tmp3
from #tmp2 t2
inner join (
    select inventbatchid,
        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'
    from #tmp2
    group by inventbatchid
) AS t2_pics on
    t2_pics.inventbatchid = t2.inventbatchid
0
 

Author Closing Comment

by:AXISHK
ID: 40503939
Tks
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question