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 |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-10-28 : 09:20:25
|
| hello,how can I display the top one record and group by something .eg.value name200 jamie100 jamie300 bob250 bobI want to show :value name200 jamie300 bobis that posible ?thank you,Jamie |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-28 : 09:43:21
|
this has got to be one of the top 5 most asked questions of all time...it is possible:select t1.*from table1 t1inner join (select max(value) as value, name from table1 group by name ) t2 on t1.value = t2.value and t1.name = t2.nameGo with the flow & have fun! Else fight the flow  |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-28 : 09:46:16
|
| you might be able to get away with:select name, max(value)from tblgroup by nameif you just want the maximum of "value" to be returned, per name.- Jeff |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-10-28 : 10:16:53
|
| hi, thanks for the reponses.however I made a mistake.my data is like :name value datejamie 300 30/06/04jamie 200 10/06/04bob 250 04/06/04bob 100 15/06/04what I need is to get the value for the max date for each name...I have tried max(date), value, but I still get all the results. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-28 : 10:17:41
|
| change value to date in my queryGo with the flow & have fun! Else fight the flow |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-10-28 : 10:29:28
|
| that doesn't work , I still get more than one row for some names. however, Ithink I have managed to do it by creating a query for maxofdate, then linking this query to the original query by name and date. seems to be ok.however I'll b back if not ! :o)ps, I am using access2000, I should have said that to begin with. sorry. |
|
|
|
|
|
|
|
|
display top 1 record grouped by name