Results 1 to 7 of 7

Thread: Min value

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Unanswered: Min value

    CurrentStage resides in the BUSLOCATION table and UserStage resides in the USERLOCATION table.

    I need to find out the MIN difference between these two columns

    i.e UserStage-CurrentStage=SHOULD BE THE MINIMUM VALUE.

    Userstage is just one value but then it shud be subtracted from all the CurrentStage values in the table and the CurrentStage value which gives me the least difference should be extracted out.

    Hope i didn't confuse u all too much :P anyone any ideas?

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    so something like

    select t1.UserStage - t2.CurrentStage from
    BUSLocation as t1
    join UserLocation as t2 on whatever your join is
    Where t1.UserStage - t2.CurrentStage = min(t1.UserStage - t2.CurrentStage )

    yeah?????

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you might need to go to....

    select t1.UserStage - t2.CurrentStage from
    BUSLocation as t1
    join UserLocation as t2 on whatever your join is
    Where t1.UserStage - t2.CurrentStage =
    (select min(t1.UserStage - t2.CurrentStage from
    BUSLocation as t1
    join UserLocation as t2 on whatever your join is )

    not sure though.....

  4. #4
    Join Date
    Nov 2003
    Posts
    31
    Do u think my code would work?

    SELECT CurrentStage, PlateNbr from tblBusLocation a, tblUserLocation b
    Where a.CurrentStage < b.UserStageNbr AND a.CurrentStage= (SELECT MIN(b.UserStageNbr-a.CurrentStage))

  5. #5
    Join Date
    Nov 2003
    Posts
    31
    Sorry here is an updated code. Is there a problem with the MIN function?

    SELECT t1.CurrentStage, t1.PlateNbr

    From tblBusLocation t1, tblUserLocation t2

    Where t1.CurrentStage < t2.UserStageNbr AND

    t1.CurrentStage =

    (SELECT t1.CurrentStage from tblBusLocation t1, tblUserLocation t2

    where MIN (t2.UserStageNbr - t1.CurrentStage)
    )

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t1.CurrentStage, t1.PlateNbr 
         , MIN (t2.UserStageNbr - t1.CurrentStage)
      From tblBusLocation t1
         , tblUserLocation t2
     Where t1.CurrentStage  < t2.UserStageNbr 
    group
        by t1.CurrentStage, t1.PlateNbr

    rudy

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    this might be way off but...

    select top 1 (t2.UserStageNbr - t1.CurrentStage)
    From tblBusLocation t1, tblUserLocation t2
    Where t1.CurrentStage < t2.UserStageNbr
    Order by (t2.UserStageNbr - t1.CurrentStage)

    ?????

Posting Permissions

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