Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2001
    Posts
    135

    Unanswered: Is This The Best Solution For Select?

    HI , I HAVE A TABLE LIKE THIS

    DUP_CODIGO AND DUP_VLDUPLICATA
    123 123,66
    123 12,88
    ...
    ...
    ....
    49 19,99
    49 23,99
    ..
    ..

    51
    51
    ETC

    I want to get the MAX VALUE FROM VLDUPLICATA AND HIS CODIGO

    THIS SELECT WORKS FINE. BUT I WOULD LIKE TO
    KNOW IF THERE ARE BEST SOLUTIONs.

    BY THE way THE RESULT FOR THIS SELECT WILL BE

    DUP_CODIGO MAXIMO
    49 23,99

    SELECT DISTINCT dup_codigo,
    (SELECT MAX(DUP_VLDUPLICATA)
    FROM DUPLICAT
    WHERE DUP_CODIGO = 49) AS MAXIMO
    FROM Duplicat
    WHERE (dup_codigo = 49)


    TKS

    Carlos Lages

  2. #2
    Join Date
    Aug 2003
    Posts
    27
    if you can find the answer to that I would like to know also.
    I have a table similiar to that and am using select statement like urs too.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    SELECT dup_codigo,MAX(DUP_VLDUPLICATA)
    FROM DUPLICAT
    group by dup_codigo

  4. #4
    Join Date
    Aug 2003
    Posts
    27
    hmm yeah y din i think of that.. neway that won't solve the problem that I'm having
    what if you need to find the max like this

    employeeid, dateeffective,effectivesequence,value
    000001,1/1/2003,0,123
    000001,1/2/2003,0,456
    000001,1/2/2003,1,789

    max of the dateeffective and effectivesequence...
    i'm doing the sql similar to the one given by Carlos. Any suggestions??

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select employeeid
         , dateeffective
         , effectivesequence
         , value
      from yourtable X
     where dateeffective =
           ( select max(dateeffective)
               from yourtable
              where employeeid = X.employeeid )
       and effectivesequence = 
           ( select max(effectivesequence)
               from yourtable
              where employeeid = X.emplyeeid
                and dateeffective =
                    ( select max(dateeffective)
                        from yourtable
                       where employeeid = X.emplyeeid )
           )
    rudy
    http://r937.com/

  6. #6
    Join Date
    Aug 2003
    Posts
    27
    that's the code that I'm having now... which is not really that efficient =(

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, there are other ways to do it (e.g. joins to derived tables)

    but perhaps you might want simply just to select the table, order by dateeffective and effectivesequence, and use a cursor or bring the entire result set into your scripting language and do it there

    i'd be interested in hearing about the EXPLAIN plans for whatever alternatives you come up with

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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