Search code examples
sql-server-2000aggregatewhere-clause

SELECT Top(1), aggregate function with Where clause

OK, I've googled and googled and still can't get this.

Effectively, in a table containing several hundred thosand rows, one column has a unique idendtifier (not a PK and not really unique, but hey) and another has numerical values.

The unique identifier (UI) is unique only within that table and is sort of incremental, in that the highest number signifies the most recent table entry.

Effectively, I need to break the rows down to relevant rows using a WHERE clause, then get the most recent UI of those rows together with the SUM of the values of those rows.

i.e. if UI are 1, 3, 5, 7, 10 and the corresponding values for the aggregate function are 100, 300, 500, 700 and 1000, what I need to have as query result is UI 10, Sum 2600.

DB is SQL2000

How do I acheive this?

Solution

  • It sounds like all of the items in the table need to be summed and returned with the max identifier. Would this work for you?

    Select Max(ID), Sum(Number) from TableName

    ID would be your Unique Identifier column name. Number would be your column name that holds the numbers. TableName is the name of your table.