The winger's first goal, allied to a Hamilton equaliser against St Mirren, saw the Perth side edge past the faltering Buddies to claim the final place in the top half of the table on goal difference alone.
After the final whistle at McDiarmid, Callum Davidson's Betfred Cup winners endured an anxious wait for confirmation that the Paisley club had dropped two points before celebrating another milestone.
But it was a bitter blow for County, who are locked in the relegation dogfight with Accies and Kilmarnock.
The Perth side enjoyed the bulk of possession and territory without breaching a solid Staggies defence until the final stages.
But just as the Staggies sensed they would leave Perth with a precious point, substitutes Stevie May and Middleton combined to end the stalemate.
Visiting keeper Ross Laidlaw twice came to the rescue of the Highlanders in the first half but slack finishing also undermined Saints' efforts to pull in front.
Northern Ireland midfielder Ali McCann tested him in the 24th minute with a raking 20-yarder and had to settle for a corner kick when the ball was palmed round a post.
A lovely touch and beautifully weighted pass from Israeli striker Guy Melamed released Chris Kane 10 minutes later but Laidlaw was alive to the danger and quickly left his line to block the striker's angled shot.
McCann needed treatment after colliding with an upright trying to clear a County corner.
Before the break, central defender Liam Gordon headed a David Wotherspoon free-kick over the target.
Rooney tried to break the deadlock himself early in the second half but his right-footed strike was deflected out for a corner by Leo Hjelde.
In the 56th minute veteran midfielder Liam Craig sensed an opening on the edge of the penalty area but his shot lacked venom and was scooped up by Staggies keeper Laidlaw.
In a rare attack, the Staggies finally caused concern in the Perth penalty area but full-back Coll Donaldson's scooped effort failed to find the target.
With 12 minutes left, Saints sub Middleton dashed through on goal but dinked his effort beyond both the goalkeeper and the target.
But he made amends minutes later to send Saints into the top six for the first time this season.