Home Forums SQL Server 7,2000 T-SQL Having and Max of a column RE: Having and Max of a column

  • October 12, 2009 at 2:31 pm

    #1064927

    saivko (10/12/2009)

    Thanks so very much Arun! I'm not used to using sub-tables, very nice to learn from simple examples.

    Seth Phelabaum, thanks also. I just wanted the ColD that was on the row that had the greatest ColF. I think that's what Arun did. But I see what you mean about another derived table if I wanted the largest ColD as well. I'm not sure what you mean by "only want 1 of colD." The code above seemed to keep it only as one already, but I might be short sighted for a scenario where a second ColD slips in?

    The reason it appears to only have 1 ColD is because the data only supplies 1 per max colF value. If you add an extra line to your test data, say:

    select 'A','AA','AAA','KN',30

    You'll now have 2 colD's for the single Max ColF of 30 and will get dupes. Just wanted to point it out.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]