T-SQL grouping question

Question

Every once and a while I have a scenario like this, and can never come up with the most efficient query to pull in the information:

Let's say we have a table with three columns (A int, B int, C int). My query needs to answer a question like this: "Tell me what the value of column C is for the largest value of column B where A = 5." A real world scenario for something like this would be 'A' is your users, 'B' is the date something happened, and 'C' is the value, where you want the most recent entry for a specific user.

I always end up with a query like this:

SELECT
    C
FROM
    MyTable
WHERE
    A = 5
    AND B = (SELECT MAX(B) FROM MyTable WHERE A = 5)

What am I missing to do this in a single query (opposed to nesting them)? Some sort of 'Having' clause?

Answer 1

I think you are close (and what you have would work). You could use something like the following:

select C
     , max(B)
  from MyTable
 where A = 5
group by C

Answer 2

After a little bit of testing, I don't think that this can be done without doing it the way you're already doing it (i.e. a subquery). Since you need the max of B and you can't get the value of C without also including that in a GROUP BY or HAVING clause, a subquery seems to be the best way.

    create table #tempints (
    a int,
    b int,
    c int
    )

    insert into #tempints values (1, 8, 10)
    insert into #tempints values (1, 8, 10)
    insert into #tempints values (2, 4, 10)
    insert into #tempints values (5, 8, 10)
    insert into #tempints values (5, 3, 10)
    insert into #tempints values (5, 7, 10)
    insert into #tempints values (5, 8, 15)

    /* this errors out with "Column '#tempints.c' is invalid in the select list because it is not contained in either an 
    aggregate function or the GROUP BY clause." */
    select t1.c, max(t1.b)
    from #tempints t1
    where t1.a=5 

    /* this errors with "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING 
    clause or a select list, and the column being aggregated is an outer reference." */
    select t1.c, max(t1.b)
    from #tempints t1, #tempints t2
    where t1.a=5 and t2.b=max(t1.b)

    /* errors with "Column '#tempints.a' is invalid in the HAVING clause because it is not contained in either an aggregate 
function or the GROUP BY clause." */
    select c
    from #tempints
    group by b, c
    having a=5 and b=max(b)

    drop table #tempints

Answer 3

You can do this:

SELECT TOP 1 C
FROM    MyTable
WHERE    A = 5
ORDER BY b DESC

Answer 4

BoSchatzberg's answer works when you only care about the 1 result where A=5. But I suspect this question is the result of a more general case. What if you want to list the top record for each distinct value of A?

SELECT t1.*
FROM MyTable t1
   INNER JOIN 
     (
       SELECT A, MAX(B)
       FROM MyTable
       GROUP BY A
     )  t2 ON t1.A = t2.A AND t1.B = t2.B

Answer 5

--
SELECT C
FROM MyTable
INNER JOIN (SELECT A, MAX(B) AS MAX_B FROM MyTable GROUP BY A) AS X
    ON MyTable.A = X.A
    AND MyTable.B = MAX_B
--
WHERE MyTable.A = 5

In this case the first section (between the comments) can also easily be moved into a view for modularity or reuse.