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-10-31 : 05:29:11
|
| [code]ALFA ROMEO 159 DIESEL SALOON 1.9 JTDM 16v Lusso 4dr 4 269.99 259.99BMW 3 SERIES COUPE 320i SE 2dr 4 361.99 319.99BMW 3 SERIES COUPE 320i SE 2dr Auto 4 397.99 344.99BMW 3 SERIES COUPE 325i SE 2dr 4 423.99 365.99BMW 3 SERIES COUPE 325i SE 2dr Auto 4 453.99 390.99BMW 3 SERIES DIESEL COUPE 320d SE 2dr 4 383.99 355.99BMW 3 SERIES DIESEL COUPE 320d SE 2dr Auto 4 406.99 380.99BMW 3 SERIES DIESEL COUPE 325d SE 2dr 4 428.99 380.99BMW 3 SERIES DIESEL COUPE 325d SE 2dr Auto 4 448.99 411.99BMW 3 SERIES DIESEL SALOON 318d ES 4dr 4 349.99 326.99BMW 3 SERIES DIESEL SALOON 318d SE 4dr 4 364.99 346.99BMW 3 SERIES DIESEL SALOON 320d ES 4dr 4 369.99 346.99BMW 3 SERIES DIESEL SALOON 320d ES 4dr Auto 4 409.99 376.99BMW 3 SERIES DIESEL SALOON 320d SE 4dr 4 389.99 361.99BMW 3 SERIES DIESEL SALOON 320d SE 4dr Auto 4 419.99 386.99BMW 3 SERIES DIESEL SALOON 325d SE 4dr 4 417.99 384.99BMW 3 SERIES DIESEL SALOON 325d SE 4dr Auto 4 450.99 414.99BMW 3 SERIES SALOON 318i ES 4dr 4 322.99 301.99BMW 3 SERIES SALOON 318i ES 4dr Auto 4 357.99 331.99BMW 3 SERIES SALOON 318i SE 4dr 4 342.99 316.99BMW 3 SERIES SALOON 318i SE 4dr Auto 4 372.99 346.99BMW 3 SERIES SALOON 320i ES 4dr 4 337.99 316.99BMW 3 SERIES SALOON 320i ES 4dr Auto 4 372.99 346.99BMW 3 SERIES SALOON 320i SE 4dr 4 357.99 336.99BMW 3 SERIES SALOON 320i SE 4dr Auto 4 392.99 366.99BMW 3 SERIES SALOON 325i SE 4dr 4 412.99 386.99BMW 3 SERIES SALOON 325i SE 4dr Auto 4 442.99 411.99BMW 5 SERIES DIESEL SALOON 520d SE 4dr 4 388.99 365.99BMW 5 SERIES DIESEL SALOON 520d SE 4dr Step Auto 4 403.99 375.99BMW 5 SERIES DIESEL SALOON 525d SE 4dr 4 469.99 427.99BMW 5 SERIES DIESEL SALOON 525d SE 4dr Step Auto 4 476.99 427.99BMW 5 SERIES DIESEL SALOON 530d SE 4dr 4 521.99 487.99BMW 5 SERIES DIESEL SALOON 530d SE 4dr Step Auto 4 541.99 492.99BMW 5 SERIES SALOON 523i SE 4dr 4 471.99 407.99BMW 5 SERIES SALOON 523i SE 4dr Step Auto 4 476.99 412.99FIAT GRANDE PUNTO HATCHBACK 1.2 Active 3dr 1 124.99 104.99FIAT GRANDE PUNTO HATCHBACK 1.2 Active 5dr 1 129.99 112.99FORD FOCUS DIESEL HATCHBACK 1.8 TDCi Style 5dr 2 211.99 186.99MERCEDES-BENZ A CLASS DIESEL HATCHBACK A160 CDI Classic SE 5dr 4 189.99 169.99MERCEDES-BENZ A CLASS HATCHBACK A150 Classic SE 5dr 4 174.99 159.99MERCEDES-BENZ C CLASS DIESEL SALOON C220 CDI SE 4dr Auto 4 332.99 339.99MERCEDES-BENZ C CLASS SALOON C180K SE 4dr Auto 4 304.99 309.99MERCEDES-BENZ C CLASS SALOON C200K SE 4dr Auto 4 323.99 326.99MERCEDES-BENZ E CLASS SALOON SPECIAL EDITION E220 CDI Executive 4dr Tip Auto 4 422.99 369.99TOYOTA VERSO DIESEL ESTATE 2.2 D-4D T Spirit 5dr 3 248.99 229.99TOYOTA VERSO DIESEL ESTATE 2.2 D-4D T2 5dr 3 193.99 183.99TOYOTA VERSO DIESEL ESTATE 2.2 D-4D T3 5dr 3 222.99 207.99TOYOTA VERSO ESTATE 1.6 VVT-i T2 5dr 3 177.99 169.99TOYOTA VERSO ESTATE 1.8 VVT-i T Spirit 5dr 3 248.99 229.99TOYOTA VERSO ESTATE 1.8 VVT-i T2 5dr 3 194.99 182.99TOYOTA VERSO ESTATE 1.8 VVT-i T3 5dr 3 222.99 206.99VAUXHALL ZAFIRA ESTATE 1.6i Life 5dr [AC] 6 189.99 177.99[/code]I need to select on this data and return only one line for each distinct model (model is column 2). The line I want is the one with the cheapest price for that model either in the last column or the second to last.Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-10-31 : 05:32:25
|
[code]select *from yourtable tinner join ( select model, price = min(price) from yourtable group by model ) m on t.model = m.model and t.price = m.price[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
GROUP BY based on MIN column1 OR column2