StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Query Adjustment
text
Body
copied!<p><strong>The initial question was as follows:</strong> <a href="https://stackoverflow.com/questions/18850908/sql-advancedquery">SQL AdvancedQuery</a> It is recommended to take a look at the initial question before going at this one, as it holds pertinent information. This question asked the following and gave the following info; </p> <ol> <li><p>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.</p> <p>MMSI's will be reoccuring in this database (as demonstrated in the data above) (this data can be found through the link if you would like to see it) 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.</p></li> <li><p>Ship_Type might change! In which case it must reflect the later date</p></li> <li><p>I only need ships which fall within the following latitude and longitude: <strong>Where Latitude > 55 and Latitude <85 and Longitude > 50 and Longitude < 141;</strong> </p></li> </ol> <p>****This is the part that has changed! I now need all records that share in an MMSI that has the correct constraints. so for example the following record:****</p> <pre><code>MMSI Message_ID Time Ship_Type Vessel_Name Latitude Longitude 2225555 1 2012-09-01 NULL NULL 25.432345 145.123343 </code></pre> <p><strong><em>which was disregarded last time will now be kept as a result of the MMSI number 2225555 having had a record within the latitude and longitudes specified.</em></strong></p> <p>The following Query worked for all the specifications in the last question: <a href="https://stackoverflow.com/questions/18850908/sql-advancedquery">SQL AdvancedQuery</a> </p> <pre><code>; WITH positions AS ( SELECT MMSI , Message_ID , "Time" , Latitude , Longitude FROM dbo.DecodedCSVMessages_Staging WHERE Message_ID IN (1, 3) AND Latitude > 55 AND Latitude < 85 AND Longitude > 50 AND Longitude < 141 ) , details AS ( SELECT MMSI , Ship_Type , Vessel_Name , Row_Number() OVER (PARTITION BY MMSI ORDER BY "Time" DESC) As row_num FROM dbo.DecodedCSVMessages_Staging WHERE Message_ID = 5 ) SELECT positions.MMSI , positions.Message_ID , positions."Time" , details.Ship_Type , details.Vessel_Name , positions.Latitude , positions.Longitude FROM positions INNER JOIN details ON details.MMSI = positions.MMSI AND details.row_num = 1 -- Limit to "latest" ship details per MMSI </code></pre> <p>I just need a slight adjustment. I am not a programmer.. So adjusting a query of this complexity for me is not at this moment possible.. Thanks.. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload