SQLTeam.com | Weblogs | Forums

Double distinct with a join

Hi. Looking for some help with this command.

I'm trying to pull up only 1 row for each ShipID but also want to limit the join to just 1 row for each distinct AgainstShipID.

The connection is via an ADODB.Connection with Microsoft.ACE.OLEDB.12.0 provider.

Help would be appreciated, Thanks.

SELECT DISTINCT ShipID, ShipName, UpdateDate, FleetID, FleetName, Trophies, Stars, DivisionID, Result, BattleDate
FROM (tblShipsMonthly LEFT JOIN tblShipBattles ON tblShipsMonthly.ShipID = tblShipBattles.AgainstShipID)
ORDER BY ShipName ASC, UpdateDate DESC, BattleDate DESC