At first, we need to identify the group of journey IDs having maximum journey version value.
SELECT
journey.JourneyID
, MAX(journey.VersionNumber) as VersionNumber
FROM _Journey AS journey
GROUP BY journey.JourneyID
Let's call the DE result is "Journey_max_ver_DE"
For other columns, we would need to inner join between the result above and _Journey
with other columns
SELECT
a.JourneyID
, a.JourneyName
, a.VersionNumber
, a.CreatedDate
, a.JourneyStatus
FROM _Journey AS a
INNER JOIN
Journey_max_ver_DE as b
ON a.JourneyID = b.JourneyID AND a.VersionNumber = b.VersionNumber
To exclude the deleted journeys, just put a condition
WHERE a.JourneyStatus != 'Deleted'
Combine everything:
SELECT
a.JourneyID
, a.JourneyName
, a.VersionNumber
, a.CreatedDate
, a.JourneyStatus
FROM _Journey AS a
INNER JOIN
(SELECT
journey.JourneyID
, MAX(journey.VersionNumber) as VersionNumber
FROM _Journey AS journey
GROUP BY journey.JourneyID) as b
ON a.JourneyID = b.JourneyID AND a.VersionNumber = b.VersionNumber
WHERE a.JourneyStatus != 'Deleted'
Note: I added an additional attribute "JourneyStatus" so we can know the current status of the latest version of the journey.