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 |
|
benko
Starting Member
24 Posts |
 Posted - 2004-10-07 : 12:08:12
|
| Im doing a query that brings back around 10 fields from some detailed records. Now 1 record might have several detailed records and what i want to do is select the max(date) of those detailed records. I know this sounds simple but it keeps saying to include all those fields in my group by clause. Any suggestions please. Thanks in advance! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-07 : 12:14:58
|
| you can't return all rows if you only want to return the MAX().what should this return:A,B,1/1/2003B,C,2/1/2003C,D,3/1/2003E,F,4/1/2003??- Jeff |
 |
|
|
benko
Starting Member
24 Posts |
Posted - 2004-10-07 : 12:22:37
|
| if i went:select id, max(date)from tblWhateverGroup by idthat will bring back all the records in that table, but if there is the same record (1, 05/25/2004)(1,05/26/2004)(1,05/27/2004). The max function will will only take the latest record...know what im sayinim just wondering if there is another way cause my query aint as simple as the one above!! im using joins and crap.. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-07 : 13:18:07
|
| I understand that. You need to tell us what you have and what you want returned. My point is that Logically you cannot return the data from all of the rows/columns if you want to only return the row with the max(date), unless you summarize the other rows in some manner (Taking the min, the max, sum(), etc). Does this make sense?- Jeff |
|
|
|
benko
Starting Member
24 Posts |
Posted - 2004-10-07 : 15:35:34
|
| Ya Jeff, sorry man my mistake. Like i tried it how you said and it still brought back all of the rows for that certain id. What i did now was i made some #tmp tables to work around that but im not sure if its the best way to do it. I have comments table, and i need to get the latest comment for all the records. So if record number 1 has 4 comments, i just need the latest one. If record 2 has the same....blah blah blah, ya know! So thats why i thought the Max(Date) would come in. What do u suggest?! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-07 : 16:05:54
|
| that's all you had to say. makes a little more sense now. give us more info, and we'll spend less time figuring out the question and more time figuring out the answer.If ID is the primary key of your table, and COmmentDate is the column you want to use to return the last row, you can simply say:select A.*from YourTable Awhere CommentDate = (select Max(CommentDate) from YourTable B where A.ID = B.ID)ORselect A.*from YOurTableAinner join (select ID, Max(CommentDate) as MaxDate from YOurTable group by ID) BON A.ID= B.ID and A.COmmentDate = B.MaxDate- Jeff |
|
|
|
|
|
|
|
|
Max Aggregate...