Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
medtech26
Posting Yak Master
169 Posts |
 Posted - 2009-08-26 : 18:14:00
|
Hello,I have the following query:SELECT TOP 100 term, SUM(rank) as R1 FROM tTable GROUP BY term ORDER BY SUM(rank) DESC, 2/MAX(tCount) DESC, LEN(term); and was wandering if the "MAX(tCount)" in the sort order bring the maximum value of all the records selected or only the grouped ("per term") ones? (if you can explain the logic behind that it would be great  )Thanks in advance |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-08-26 : 19:56:14
|
| I'll give it a try...The MAX(tCount) will be maximum value per Term; not the maximum value of the entire table.When you use any of the aggregate functions (MIN, MAX, SUM, COUNT, et al) and you are grouping by a column or columns, the aggregate is always a value determined by the raw data within that grouping. Let's note, in passing, that if you don't explicitly group things you are de facto grouping by the entire table. When you aggregate, you are in essence producing a virtual table.Perhaps an example can clarify:Suppose your table contains a million rows and there are 500 distinct "Terms". When you group by the Terms column, you are producing exactly 500 virtual rows. The columns of that virtual table correspond to the grouping column(s), in your case the Terms column only, and a column for each aggregate function that you use. In this case that means a column for the SUM and a column for the MAX. The FROM clause and the GROUP BY clause work together to create the virtual table. The raw data from the tTable is used to determine the values for the SUM and MAX columns.The SELECT clause and the ORDER BY clause actually work against the virtual table; not the raw tTable. As far as these two clauses are concerned, there are only three columns: Term, Sum and Max. The ORDER BY orders the 500 rows and the SELECT takes the top 100 and ignores the MAX column.hopefully I have clarified things but I won't be surprised if I have rendered a simple concept into indecipherable gibberish.The FROM The SELECT clause returns data from this virtual table=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
 |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-08-26 : 20:17:43
|
| That's what I was thinking/hoping/wanted and you made it clear, thanks. |
|
|
|
|
|
|
|
|
SQL 2005 Max function