• rkordonsky 63916 - Tuesday, December 12, 2017 12:21 PM

    Thom A - Tuesday, December 12, 2017 11:21 AM

    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