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 |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2006-03-13 : 19:10:35
|
| I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.The request should select the rows that have Price equal 100 or 120Thanks a lot for help |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-13 : 19:39:00
|
| This looks a lot like homework. I would look at using the TOP keyword and an IN clause. look these up in BOL for more details.-ec |
 |
|
|
tafigueroa
Starting Member
2 Posts |
Posted - 2006-03-13 : 22:59:03
|
| There are two possible solutions depending on what it is you are truly trying to get to:1. If you only want to see the top 2 values within the price column then you would write your SQL like below.Select Top 2 PriceFROM TableOrder By Price DESCGroup By Price2. If you are trying to find all the records in the table which have prices which are equal to the top 2 price values in the talbe then you would write your SQL like below.Select T.Field1, T.Field2, T.Price, etcFrom Table AS TWhere T.Price = (Select Top 2 T2.Price From Table AS T2 Order By T2.Price Desc Group By T2.Price) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-13 : 23:13:50
|
| wait, are we now directly answering homework questions?-ec |
|
|
|
tafigueroa
Starting Member
2 Posts |
Posted - 2006-03-13 : 23:22:12
|
| Correct, my mistake. Also, one more mistake made...in your query put the Order By after the Group by.That's what I get for typing quickly.Enjoy... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Selecting the two biggest values of a field