SQL help (can't think of a more descriptive subject)

[mluken]

I have an SQL problem that I am not sure how to solve. I am a java guy and SQL is not really my specialty. Basically I have a table setup something like this:


Table name: LOADS

LOAD_NUMBER SEQUENCE_NUMBER LOCATION

1100 1 Des Moines
1100 2 Ames
1100 3 Kansas City
1500 1 St Louis
1500 2 Chicago

So basically the LOAD_NUMBER and SEQUENCE_NUMBER make up a unique entry. What I need is a query that will return a result set containing a list of all the loads (one record per load number) at the highest sequence number. So in this example, the result would be Kansas City and Chicago.

I have been playing around with this for awhile, but can't quite seem to get it. Something like:

SELECT
MAX(SEQUENCE_NUMBER) AS [SEQUENCE_NUMBER],
LOCATION
FROM
LOADS
WHERE
UNIQUE(LOAD_NUMBER) // I know this isn't correct syntax, but something like this?

Any thoughts at all?

Thanks!!!!!!!!

Mike

 

[r937]

select LOAD_NUMBER
     , SEQUENCE_NUMBER  
     , LOCATION
  from LOADS AS ZZ     
 where SEQUENCE_NUMBER
     = ( select max(SEQUENCE_NUMBER) 
           from LOADS
          where LOAD_NUMBER
              = ZZ.LOAD_NUMBER )

rudy

http://r937.com/

SQL Consulting