Results 1 to 7 of 7

Thread: Min not working

  1. #1
    Join Date
    Aug 2006
    Posts
    12

    Unhappy Unanswered: Min not working

    Hi all:
    Because me not this functioning the following one query.

    simply me not the function respects min, because?, that I am doing badly?
    Select Distinct(Min(c.gentime)),
    a.CardNumber,a.CardHolderId,a.Deleted,
    b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
    c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
    From Card a,
    CardHolder b,
    History c
    Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
    And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
    And(a.cardnumber Between 1500 And 1600)
    And(b.note4 <> 'Mantenimiento')
    And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
    And(c.Link1=10)
    Group By a.CardNumber, a.CardHolderID, a.Deleted,
    b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
    c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
    Order By a.CardNumber,c.recvtime

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is because you have c.Link4 in the GROUP BY but not in the SELECT

    also, please note: DISTINCT is not a function

    try this --
    Code:
    select Min(c.gentime)   as min_gentime
         , a.CardNumber
         , a.CardHolderId
         , a.Deleted
         , b.RecordId
         , b.FirstName
         , b.LastName
         , b.Deleted
         , b.Note4
         , c.param3
         , c.param2
         , c.param1
         , c.recvtime
         , c.gentime
         , c.link1
         , c.link2
         , c.link3
         , c.deleted
         , c.recordid
         , c.seqid
      from Card a
    inner
      join CardHolder b
        on b.recordid = a.cardholderid
    inner
      join History c
        on c.link3 = b.recordid
     where a.Deleted = 0
       and b.Deleted = 0
       and c.deleted = 0
       and a.cardnumber between 1500 and 1600
       and b.note4 <> 'Mantenimiento'
       and c.RecvTime >= '2006-10-01'
       and c.RecvTime <  '2006-10-13'
       and c.Link1 = 10
    group
        by a.CardNumber
         , a.CardHolderId
         , a.Deleted
         , b.RecordId
         , b.FirstName
         , b.LastName
         , b.Deleted
         , b.Note4
         , c.param3
         , c.param2
         , c.param1
         , c.recvtime
         , c.gentime
         , c.link1
         , c.link2
         , c.link3
         , c.deleted
         , c.recordid
         , c.seqid
    order
        by a.CardNumber
         , c.recvtime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    12

    Question

    thanks by answering, the problem is, that the inner join does not respect me the rank dated, I need a value by each employee, by each I gave of the rank.

    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, try this --
    Code:
    select c.gentime   as min_gentime
         , a.CardNumber
         , a.CardHolderId
         , a.Deleted
         , b.RecordId
         , b.FirstName
         , b.LastName
         , b.Deleted
         , b.Note4
         , c.param3
         , c.param2
         , c.param1
         , c.recvtime
         , c.gentime
         , c.link1
         , c.link2
         , c.link3
         , c.deleted
         , c.recordid
         , c.seqid
      from Card a
    inner
      join CardHolder b
        on b.recordid = a.cardholderid
    inner
      join History c
        on c.link3 = b.recordid
     where a.Deleted = 0
       and b.Deleted = 0
       and c.deleted = 0
       and a.cardnumber between 1500 and 1600
       and b.note4 <> 'Mantenimiento'
       and c.RecvTime >= '2006-10-01'
       and c.RecvTime <  '2006-10-13'
       and c.Link1 = 10
       and c.gentime =
           ( select min(c.gentime)
               from History
              where link3 = b.recordid
                and deleted = 0
                and RecvTime >= '2006-10-01'  
                and RecvTime <  '2006-10-13'  
                and Link1 = 10 )
    order
        by a.CardNumber
         , c.recvtime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    12

    Red face

    thanks but the following error marks me:

    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm going to move this query to the SQL Server forum

    i don't understand the error message, maybe someone else does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not completely sure what is going on here, but trying moving the subquery to the JOIN clause:
    Code:
    select c.gentime   as min_gentime
         , a.CardNumber
         , a.CardHolderId
         , a.Deleted
         , b.RecordId
         , b.FirstName
         , b.LastName
         , b.Deleted
         , b.Note4
         , c.param3
         , c.param2
         , c.param1
         , c.recvtime
         , c.gentime
         , c.link1
         , c.link2
         , c.link3
         , c.deleted
         , c.recordid
         , c.seqid
      from Card a
    inner
      join CardHolder b
        on b.recordid = a.cardholderid
    inner
      join History c
        on c.link3 = b.recordid
    inner
      join --subquery
        (select link3,
                min(c.gentime) as gentime
            from History
         where link3 = b.recordid
            and deleted = 0
            and RecvTime >= '2006-10-01'  
            and RecvTime <  '2006-10-13'  
            and Link1 = 10
         group by link3) subquery
         on subquery.link3 = b.recordid
            and subquery.gentime = c.gentime 
     where a.Deleted = 0
       and b.Deleted = 0
       and c.deleted = 0
       and a.cardnumber between 1500 and 1600
       and b.note4 <> 'Mantenimiento'
       and c.RecvTime >= '2006-10-01'
       and c.RecvTime <  '2006-10-13'
       and c.Link1 = 10
    order
        by a.CardNumber
         , c.recvtime
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •