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?
The reason this query doesn't work has nothing to do with Thom's suggestion. You have a subquery that returns two columns, which is not allowed in most circumstances (APPLY being the main exception).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA