Add an
ORDER BY clause. Data inside SQL Server isn't stored in any specific order, it's all a "heap". When you use a TOP 1, without an ORDER BY, the result you get back will simply be the first row in the dataset created; which could be anything. Adding an ORDER BY defines the order (surprise that!) the data is returned in, thus resulting in the TOP 1 being the first ordered row.
Try yourself, and post back if you get stuck.
Thank you, Thom A. I did not form my question correctly. What I was interested is how to get ST_ID data for TOP record.
The query:
SELECT * FROM Client WHERE ID IN(Select TOP(1) SUM(TOTAL_PAYMENTS) as highest, ST_ID FROM ORDERS GROUP BY ST_ID ORDER BY highest DESC
obviously would not work. Any thoughts?