SELECT Lawson_Id, Location_Name, Venue_Type_Description,rownum
FROM (SELECT
          Lawson_Id
          ,Location_Name
          ,vl.Venue_Type_Description
          ,ROW_NUMBER() OVER (PARTITION BY Lawson_Id ORDER BY Lawson_Id) rownum
      FROM SQL2.HardRock_Master.dbo.Location
      INNER JOIN SQL2.HardRock_Master.dbo.Venue_Lookup vl on (vl.Venue_Type_Id = Location.Venue_Type_Id)
      WHERE Lawson_Id IS NOT NULL
 ) tmp
 where
    tmp.rownum = 1
    and Lawson_Id != 0
GO