Solved

MAX function

Posted on 2014-12-15
7
95 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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