Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    22

    Question Unanswered: Query only Last Entry for Specific Column

    I need a query that displays only the last entries for each different status.

    For example when querying the following:

    table1
    status location
    image a1
    scanning a2
    editing a3
    scanning a4
    dp a5
    editing a6
    image a7

    The query returns:

    status location
    scanning a4
    dp a5
    editing a6
    image a7

    Thanks in advance...

  2. #2
    Join Date
    Jun 2006
    Posts
    22

    Lightbulb

    I think I got it... Something along the lines of:

    SELECT rh.batch, rh.location, rh.occurred
    FROM carts.dbo.CartTransactions rh, (SELECT MAX(occurred) as maxoccurred, batch FROM carts.dbo.CartTransactions GROUP BY batch) maxresults
    WHERE rh.batch = maxresults.batch AND rh.occurred = maxresults.maxoccurred;

    SQL: MAX Function

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Nebraska_God View Post
    I think I got it...
    yup, except i would've written it with explicit JOIN syntax...
    Code:
    SELECT rh.batch
         , rh.location
         , rh.occurred
      FROM ( SELECT MAX(occurred) as maxoccurred
                  , batch 
               FROM carts.dbo.CartTransactions 
             GROUP 
                 BY batch ) maxresults
    INNER
      JOIN carts.dbo.CartTransactions rh
        ON rh.batch = maxresults.batch 
       AND rh.occurred = maxresults.maxoccurred
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2006
    Posts
    22
    Thanks for responding, I actually have another question. I have two tables, one of all locations and one of transactions. I want to query all the locations in the locations table that are not part of the maxresult in the transactions table. Hopefully that makes sense.

    Thanks again for your help.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    locations? "part of" the maxresult?

    could give just a wee bit more information?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2006
    Posts
    22
    The locations table contains locations, say 1-20 with a description for each location - no current process can have the same location.

    The transaction table contains all transactions, each item can be stored at whatever location is open. I'd like the query to return all the open locations which basically means listing all the locations that are not currently the last item location in the transaction database. I might be confusing myself...

  7. #7
    Join Date
    Jun 2006
    Posts
    22
    I got it, instead of doing an inner join on a certain result set I used WHERE with 'NOT IN' which seems to work perfectly.

Posting Permissions

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