Middle Number Needs GROUP BY

  • I can't figure out the GROUP BY.

    This result should be:

    A 2

    B 7

    C 8

    Thanks!

    IF OBJECT_ID('tempdb..#TempMid') > 0 DROP TABLE #TempMid

    CREATE TABLE #TempMid (Id varchar(20), Amount numeric(18,2))

    INSERT INTO #TempMid

    (Id, Amount)

    SELECT 'A', 1 UNION ALL

    SELECT 'A', 2 UNION ALL

    SELECT 'A', 3 UNION ALL

    SELECT 'B', 5 UNION ALL

    SELECT 'B', 6 UNION ALL

    SELECT 'B', 7 UNION ALL

    SELECT 'C', 7 UNION ALL

    SELECT 'C', 8 UNION ALL

    SELECT 'C', 9

    SELECT TOP 1 Amount

    FROM(SELECT TOP 50 PERCENT Amount

    FROM #TempMid (nolock)

    ORDER BY Amount ASC) as T1

    ORDER BY 1 DESC

  • SELECT Id, AVG(Amount)

    FROM #TempMid

    GROUP BY Id

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Are you trying to get the mean (average) or median?

    And why is B 7 and not 6?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Median and yes 6. I'll search median here, thanks. Found it; I can't remember what I was originally searching by, wasn't median.

    http://www.sqlservercentral.com/Forums/Topic1205512-392-1.aspx

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply