Re: sql max help

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Your inner select is returning just 20130607...

which makes the outer statement look like:
SELECT TISEQ FROM tapeinv00 WHERE titapedate = 20130607...

So it's obvious why two rows are being returned...

You need to be more specific in your outer where:
SELECT TISEQ
FROM tapeinv00
WHERE  tisystem = 'RG'
              and TIlabel = 'FRIDAY'
              and tilocate = 'VAULT'
       and
titapedate = (select

max(titapedate)
                     
from tapeinv00
                     
where tisystem = 'RG'
                       
and TIlabel =

'FRIDAY'
                       
and tilocate = 'VAULT' )


Charles



On Tue, Nov 26, 2013 at 2:01 PM, Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>wrote:

> I have a table with the following
> TISYSTEM    TILABEL     TILOCATE    TISEQ  TITAPEDATE
> RG                 FRIDAY       VAULT          1          20,130,614
> RG                 FRIDAY       VAULT          2          20,130,524
> RG                 FRIDAY       VAULT          3          20,130,531
> RG                FRIDAY        VAULT          4          20,130,607
> RG               MONDAY   VAULT           1         20,130,614
> RG                MONDAY   VAULT          2          20,130,524
> RG                MONDAY   VAULT          3          20,130,531
> RG               MONDAY   VAULT           4          20,130,607
>
> I am trying to get the value of TISEQ for the record with the highest date
> for a specific system, label, location
>
> So I have the following query and it works fairly well, except I seem to
> get two records back
>
> SELECT TISEQ FROM tapeinv00 WHERE titapedate = (select
> max(titapedate) from tapeinv00 where tisystem = 'RG' and TIlabel =
> 'FRIDAY' and tilocate = 'VAULT' )
>
> What I would hope to get for this query is 1
> I get two records with 1 returned and I'm not sure why.
> Ideas?
> Better way to do this query?
>
> Thanks
>
> Mike
>
>
>
> NOTICE: This message, including any attachment, is intended as a
> confidential and privileged communication. If you have received this
> message in error, or are not the named recipient(s), please immediately
> notify the sender and delete this message.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.