title: SQL AdvancedQuery
body: <p>I have the following data in a table called: dbo.DecodedCSVMessages_Staging   </p>
  
  <p><strong>Here is a sample of the data:</strong></p>
  
  <pre><code>MMSI     Message_ID    Time     Ship_Type  Vessel_Name   Latitude   Longitude
  
  2102930      3       2012-06-01     NULL      NULL       56.528003  85.233443
  
  2102930      5       2012-07-01      70     RIO_CUBAL      NULL      NULL
  
  2109300      1       2012-08-01     NULL      NULL       57.43234   131.123343
  
  2109300      1       2012-09-01     NULL      NULL       62.432345  121.123343
  
  2109300      1       2012-09-02     NULL      NULL       65.432345  140.123343
  
  2109300      5       2012-08-01      70     RIO_CUBAL      NULL       NULL
  
  2225555      1       2012-08-01     NULL      NULL       66.432345  143.123343
  
  2225555      1       2012-09-01     NULL      NULL       25.432345  145.123343
  
  2225555      5       2012-08-01      70      RIO_II        NULL       NULL
  </code></pre>
  
  <p>What I need is as follows:</p>
  
  <ul>
  <li>An MMSI number is a unique number that represents a ship. A ship sends out different information in unique Message types. I am interested in only Message_ID 1's and 3's as they have Latitudinal and Longitudinal Information. MMSI's will be reoccuring in this database (as demonstrated in the data above)</li>
  <li>Problem is, I need information about the ship which is unfortunately only available in Message_ID 5's. such as Vessel_name and Ship_type. </li>
  <li><p>I only need ships which fall within the following longitude and latitude</p></li>
  <li><p><strong>Ship_Type might change! In which case it must reflect the later date</strong></p>
  
  <p><strong>where Latitude > 55 and Latitude &lt;85 and Longitude > 50 and Longitude &lt; 141;</strong></p></li>
  </ul>
  
  <p><strong>The result of querying the data</strong> would produce the following (Of which I will save to CSV format as I am using this information to build point features in ArcGIS)</p>
  
  <pre><code>MMSI     Message_ID    Time    Ship_Type  Vessel_Name   Latitude   Longitude
  
  2102930      3       2012-06-01     70     RIO_CUBAL    56.528003  85.233443
  
  2109300      1       2012-08-01     70     RIO_CUBAL    57.43234   131.123343
  
  2109300      1       2012-09-01     70     RIO_CUBAL    62.432345  121.123343
  
  2109300      1       2012-09-02     70     RIO_CUBAL    65.432345  140.123343
  
  2225555      1       2012-08-01     70      RIO_II      66.432345  143.123343
  </code></pre>
  
  <ul>
  <li><p>So all the Message_ID 5's are gone</p></li>
  <li><p>The second to last row is gone (as the Latitude was only 25.432345)</p></li>
  <li><p>The information Ship_Type and Vessel_Name has been added to the
  Message_ID 1's and 3's by way of linking using the MMSI.</p></li>
  </ul>
  
  <p>RESTRAINTS:</p>
  
  <ol>
  <li>Cannot create a new table (do no have access)</li>
  <li>There are over a billion rows of data in this database (only about 4.3 million that meet the latitude/longitude constraints though</li>
  </ol>
  
  <p><strong>WHAT MIGHT THE QUERY BE??? tHANKS!</strong></p>