Home Forums SQL Server 2005 SQL Server 2005 General Discussion combine two separate queries RE: combine two separate queries

  • February 27, 2015 at 1:54 pm

    #1780785

    i forgot the alias for the max:

    MAX(ais_data.mssisdtg1) AS mssisdtg1

    select

    ships.name,

    ais_data.mmsi,

    ships.country,

    ais_data.course,

    ais_data.sog,

    ais_data.heading,

    ais_data.latitude,

    ais_data.longitude

    from ships LEFT JOIN ais_data ON ships.mmsi=ais_data.mmsi

    INNER JOIN (select ais_data.mmsi,MAX(ais_data.mssisdtg1) AS mssisdtg1 from ais_data where ais_data.dtg='2015-02-27'

    and ais_data.message BETWEEN 1 AND 3 group by ais_data.mmsi) MyAlias

    ON ais_data.mmsi=MyAlias.mmsi

    and ais_data.mssisdtg1=MyAlias.mssisdtg1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!