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 |
|
GVeers
Starting Member
9 Posts |
 Posted - 2011-02-23 : 11:46:04
|
Hello all,I have a question about a SELECT...WHERE...GROUP BY query. Is it possible to constrain the results using the WHERE clause to take the minimum of a particular column?E.g. I have three columns, AGE, NAME, and HEIGHT.Is there a way to GROUP the set by AGE and NAME, but also get the records with minimum HEIGHT within that grouping?Thank you!  |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-02-23 : 11:54:15
|
Can you please give example data and the wanted result in relation to the sample data?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
GVeers
Starting Member
9 Posts |
Posted - 2011-02-23 : 12:14:52
|
| Certainly.AGE NAME HEIGHT18 Joe 6'18 Joe 5.5'18 Joe 6.5'19 Joe 5'19 Tom 6'The query would result in18 Joe 5.5'19 Joe 5'19 Tom 6'because it first groups by Age and Name and then picks the record with the minimum height within that grouping. Also please note the dataset would have many other columns associated with each record. I hope this helps. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-23 : 13:24:15
|
| hope this what you are looking for select age,name,MIN(height) from TableNamegroup by age,nameCheersMIK |
|
|
|
GVeers
Starting Member
9 Posts |
Posted - 2011-02-23 : 13:44:38
|
| Hi Mik, thank you for the reply.That's not quite what I'm looking for because I would like to retrieve the entire record. To do that with your query I would need to list every column name which seems cumbersome. Is there a way to shift the min requirement to the WHERE clause so I can do "SELECT *"?Cheers,GVeers |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-23 : 13:55:21
|
| No, Select * cannot be performed using group functions. while performing aggregate operations you must explicitly specify All Columns of the Select, in the Group By Clause e.g. Select col1,col2,Col3,...,MIN(ColN)From TableName Group by col1,col2,Col3,...Using where clause you can only restrict the number of rows onto which the Select Operation should perform Your requirements should have been fulfilled via above type of select and group by statement (in combination of Where clause if need to operate only on some specific rows). IF not then come up with an example (in detail) and let us know How your data looks like at present in tables (come up with few rows for all columns that needs to be displayed), and the desired output CheersMIK |
|
|
|
|
|
|
|
|
Where clause minimum