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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2002-03-14 : 13:49:44
|
| Hi I'm running into problems returning the proper data.I have a table that has four columns one of which is a date field. I need to return the row where field4 has the maximum date. The other columns have varying data.field1, field2, field3, field41, car, grass, 1/1/20013, house, plant, 2/2/20028, bike, weed, 9/7/2001I need to just return the row with the highest date. (3, house, plant, 2/2/2002)I tried:select field1,field2,field3,Max(field4) from tablebut If get "no aggregate function or Group By clause" errror. I can't group by the other columns since they have different values. I know the answer is simple but I'm being dumb.Nic |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-14 : 13:53:35
|
| select *from tblwhere field4 = (select max(field4) from tbl)Use top 1 if you may have duplicates and only want 1.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
dsdeming
479 Posts |
Posted - 2002-03-14 : 13:53:36
|
| Just add GROUP BY field1,field2,field3 to the end. You always have to have a group by clause when using aggregate functions like MAX(). |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2002-03-14 : 13:55:12
|
| I think I solve my own question:SELECT field1, field2, field3, field4 FROM table where field4 = (select MAX(field4) from table) ORDER BY field1Nic***edit*** Thanks nr you beat me to it. I knew as soon as I posted it, I'd figure it out.***edit***Edited by - nic on 03/14/2002 14:00:08 |
|
|
|
yahazim
Starting Member
13 Posts |
Posted - 2002-03-14 : 14:03:21
|
| SELECT field1, field2, field3, field4FROM tableWHERE field4 = (SELECT Max(field4) FROM table)Jim |
|
|
|
|
|
|
|
|
I'm dumb, simple tsql statement