StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can select the Type 1 and Type 3 messages, with added information from a join to the first corresponding Type 5 record in your data. (If there is no corresponding Type 5 record, you will get nulls for those fields.) Try this:</p> <pre><code>SELECT DISTINCT M13.MMSI, M13.Message_ID, M13.Time, M13.Latitude, M13.Longitude, M5.Vessel_Name, M5.Ship_Type, M5.IMO, M5.Dimension_to_Bow M5.Dimension_to_stern, M5.Dimension_to_port, M5.Dimension_to_starboard, M5.Draught FROM dbo.DecodedCSVMessages_Staging M13 JOIN ( SELECT MMSI, Time, Vessel_Name, Ship_Type, IMO, Dimension_to_Bow Dimension_to_stern, Dimension_to_port, Dimension_to_starboard, Draught FROM dbo.DecodedCSVMessages_Staging WHERE Message_ID = 5 ORDER BY Time ) M5 ON M5.MMSI = M13.MMSI WHERE M13.Message_ID IN (1, 3) AND M13.Latitude > 55 AND M13.Latitude < 85 AND M13.Longitude > 50 AND M13.Longitude < 141 ORDER BY M13.Time </code></pre> <p>If this returns the data you want, then create a new table and insert the records using <a href="http://msdn.microsoft.com/en-us/library/ms174335.aspx/css#OtherTables" rel="nofollow">INSERT INTO NewTable SELECT</a>.</p> <p>Please be aware that MMSI is not a primary key, either in the old table or in the new table. Primary keys (PK) have to be unique, and in this case you have multiple entries for each MMSI.</p> <p>When you create your new table, you should add an integer IDENTITY field so that it will have a PK. This IDENTITY field isn't included in the insert statement, but it is automatically populated with an incrementing integer. That way each record gets a unique PK, which is highly desirable.</p> <p><strong>EDITED</strong> to use a <code>DISTINCT</code> on the outer query.</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