Home Forums SQL Server 7,2000 T-SQL Weird "Group By" Behavior from Subquery with Derived Table RE: Weird "Group By" Behavior from Subquery with Derived Table

  • It looks like the sort of bug where the parser designers never tested a correlated derived table in a select list used with aggregates.  This should instead give you an error message, as derived tables consisting of correlated subqueries are generally not allowed.  I think it's performing the correlation before aggregating, essentially overriding the aggregation of the first MIN().  Your first formulation is better, anyway, and you could also (as you like derived tables ) try:

    SELECT c.defendant_sid,

        count(*) as [count],

        CAST(MIN(c.calendar_time) as char(20)) as [MIN(calendar_time)], 

        CAST(x.xMin) as char(20)) as [Min2]

    FROM #temp c JOIN

    (SELECT Defendent_SId, MIN(Calendar_Time) xMin

     FROM #temp

     GROUP BY Defendant_SId) x ON x.Defendant_SId = c.Defendant_SId

    WHERE c.defendant_sid < 10

    GROUP BY c.defendant_sid, x.xMin

    ORDER BY c.defendant_sid



    --Jonathan