Have Geek Brain

T-SQL grouping question

I have a scenario like this from time to time and can never find the most efficient query to get information:

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

I always get a request 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 (as opposed to nesting them)? Any "suggestion"?

0


a source to share


5 answers


BoSchatzberg's answer works when you only care about 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 entry for each distinct A value?



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
+4


a source


You can do it:



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


a source 


--
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 comments) can also be easily moved into the view for modularity or reuse.

+2


a source


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

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


a source


After a bit of testing, I don't think it can be done without doing it the way you do it (i.e. a subquery). Since you want the maximum of B, and you cannot get the value of C without including that in the GROUP BY or HAVING clause, the subquery seems to be the best.

    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
-1


a source






More articles:


All Articles