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 |
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
 Posted - 2007-08-08 : 06:15:44
|
| This page contains a datagridhttp://ematrix.genusvs.co.uk/test.aspxFor each unique model, for example GRANDE PUNTO HATCHBACK I want to return the lowest ThreeYearPrice and everything else from that row. In effect I need to end up with one row for each distinct model.Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-08 : 06:18:36
|
| Not going to follow the link but something like this - change id to whatever your unique columns are.select t.*from tbl tjoin (select id, ThreeYearPrice = min(ThreeYearPrice) from tbl group by id) aon t.id = a.idand t.ThreeYearPrice = a.ThreeYearPrice ==========================================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. |
 |
|
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
Posted - 2007-08-08 : 07:46:52
|
| Thanks, there is no unique ID for the rows as the table is actually a complex view coming from other tables?Is there anyway to do it without a primary key? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-08 : 07:58:49
|
| You need something that defines a group otherwise how do you define the lowest ThreeYearPrice?Whatever defines the group - use those columns instead of ID.It a problem with the requirement description.Sounds like the model would be the grouping item.==========================================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. |
|
|
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
Posted - 2007-08-09 : 16:06:14
|
| Thanks a lot. Works great on the model field :) |
|
|
|
|
|
|
Grouping query