| Author |
Topic |
|
projecttoday
Starting Member
31 Posts |
 Posted - 2004-12-29 : 12:26:09
|
| What's the best way to get fields from a record found via an aggregate function. I'm using MAX to find records and I also need some of the fields in these records besides the field in the GROUP BY clause. I have discovered that I can do this by adding these fields to the GROUP BY clause even though they don't belong there logically. Is this a good idea? Or would it be better to join the table to itself? Robert |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-29 : 13:00:23
|
| It's usually best to keep the number of entries in the group by as small as possible.If these are unique for the group (sounds like it) then you can use max on these as well.Doesn't matter too much on small sets but if you end up with a lot of entries in the group by it can affect performance.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
projecttoday
Starting Member
31 Posts |
Posted - 2004-12-29 : 13:27:35
|
| Why would I want to do a max on the other fields? They are just fields and do not define anything. But they are necessary input for my program. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-29 : 13:33:17
|
To get the other columns:SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4FROM ( SELECT Column1, MAX(Column2) AS Column2 FROM Table1 GROUP BY Column1 ) tINNER JOIN Table1 t2ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2 Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-29 : 14:32:59
|
Well done, Tara, you've made me very proud ! Not bad for a DBA!  In almost all cases, if you find you are grouping by columns that are not primary key columns in the tables involved, then it means it is time to take your query and break it apart into smaller subqueries.- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-29 : 14:38:27
|
quote:
Originally posted by jsmith8858
Well done, Tara, you've made me very proud ! Not bad for a DBA!
 Learned that technique here about a year ago.I've certainly improved my T-SQL skills by checking out solutions posted by you, Damian, Rob, Brett, and several others. It's allowed me to do quite a bit of development work with the current project that I am on.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-29 : 14:50:37
|
| Only works if a unique index is being returned from the table being joined.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-29 : 14:54:31
|
quote:
Originally posted by nr
Only works if a unique index is being returned from the table being joined.
Yep. I'll be sure to mention that in the future.Tara |
|
|
|
projecttoday
Starting Member
31 Posts |
Posted - 2004-12-29 : 17:07:57
|
| SELECT Column1, MAX(Column2), Column3, Column4, Column5FROM Table1GROUP BY Column1, Column3, Column4, Column5seems to work. Good or bad? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-29 : 17:18:50
|
| depends if the group by columns all form unique groups. If Column1, Column5 are the only grouping fields and Column3 and Column4 are always the same within those groups thenSELECT Column1, MAX(Column2), max(Column3), max(Column4), Column5FROM Table1GROUP BY Column1, Column5Would be better==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
projecttoday
Starting Member
31 Posts |
Posted - 2004-12-29 : 21:02:50
|
| What if columns 3 and 4 are text fields? |
|
|
|
projecttoday
Starting Member
31 Posts |
Posted - 2004-12-29 : 21:18:03
|
| I guess jsmith answered the question (which is what I was thinking). Thanks, j. |
|
|
|
|
Agregate function question