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?
Thom did not say anything about adding a subquery, just an ORDER BY clause.
Drew
Obviously he did not. I have to apologize for not forming the question correctly. Sorry for that.
This is why we ask for expected results as well as sample data. Instead of trying to describe what you want, show what you want.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA