Solved

MAX function

Posted on 2014-12-15
7
89 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 48

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 48

Expert Comment

by:PortletPaul
ID: 40501978
which record will be returned by "MAX" function ?
c:\photo\ac135.jpg
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Accepted Solution

by:
PortletPaul earned 300 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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