Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Unanswered: Query the latest info

    Hi All,

    I have thsi list of record

    Chasis Status Date
    pl1 sold 10/20/2004
    pl1 return 10/21/2004
    pl2 sold 10/24/2004
    pl2 return 10/25/2004
    pl3 sold 11/01/2004
    pl4 sold 11/03/2004
    pl4 return 11/04/2004
    pl4 sold 11/06/2004


    sp i want to list out cars that status solid has been sold

    so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

    Regards,
    Shaffiq

  2. #2
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    You want to list only the cars that have been sold?

    Select * from table where status = 'sold'

  3. #3
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Quote Originally Posted by pshisbey
    You want to list only the cars that have been sold?

    Select * from table where status = 'sold'
    hi,
    its easy, but sometime cars that has been sold were return back , so this not considered sold. I want to display cars that solid has been sold....sometimes got status sold, return and sold back.... but sometimes only sold..for this case only Pl3 and PL4.. any idea? thanks

  4. #4
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    There's probably a more efficient way with joins but this may work:

    select * from table where chasis not in (select chasis from table where status = 'return')

  5. #5
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Quote Originally Posted by pshisbey
    There's probably a more efficient way with joins but this may work:

    select * from table where chasis not in (select chasis from table where status = 'return')
    Thx,
    I' d tried before but it will not display cars that have tree status( sold, return, sold) for this case you can see at PL4

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think this might do it --
    Code:
    select chasis
      from yourtable as X
     where [Date] =
           ( select max([Date])
               from yourtable
              where chasis
                  = X.chasis )
       and Status = 'sold'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Quote Originally Posted by r937
    i think this might do it --
    Code:
    select chasis
      from yourtable as X
     where [Date] =
           ( select max([Date])
               from yourtable
              where chasis
                  = X.chasis )
       and Status = 'sold'

    Hi r937,

    Thx for the solution it works well thank also to pshisbey

Posting Permissions

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