Select Max. From each subset

Question

I am banging my head. I feel pretty stupid because I'm sure I did something like this before, but I can't let my life remember me. One of those days that I guess>. <

Say I have the following data: ---> and a query that returns this: ---> But I want this:

ID FirstID ID FirstID ID FirstID -- ------- -- ------- -- ------- 1 1 1 1 7 1 2 1 3 3 3 3 3 3 4 4 6 4 4 4 5 5 5 5 5 5 6 4 7 1 

Please note that my query returns records where ID = FirstID, but I want it to return Max (ID) for each subset of the unique FirstID. Sounds simple enough? This is what I thought, but I keep returning only to record number 7. Here is my query (the one that returns the second block of digits above) with some test code to make your life easier. I need this to give me results in the far right block. It should be noted that this is a self-join table, where FirstID is the foreign key to the ID. Thanks:)

 declare @MyTable table (ID int, FirstID int) insert into @MyTable values (1,1),(2,1),(3,3),(4,4),(5,5),(6,4),(7,1) select ID, FirstID from @MyTable where ID = FirstID 

Answer 1

Does it work

 declare @MyTable table (ID int, FirstID int) insert into @MyTable values (1,1),(2,1),(3,3),(4,4),(5,5),(6,4),(7,1) Select FirstID, Max (Id) ID From @MyTable Group BY FirstID 

Results in

 FirstID ID ----------- ----------- 1 7 3 3 4 6 5 5 

Answer 2

In versions of SQL2005 and later versions of SQL2008, Aggregate functionality on SQL Server has been improved

You can use the PARTITION BY clause, for example, with the functions MAX, MIN, SUM, COUNT.

Please try the following example.

select Distinct FirstID, Max (ID) OVER (PARTITION by FirstID) MaxID from @MyTable

You can find an example at http://www.kodyaz.com/t-sql/sql-count-function-with-partition-by-clause.aspx