Filter by top 2 values AND one other

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filter by top 2 values AND one other

Author

Topic

Danw6232
Starting Member

1 Post

Posted - 2012-02-05 : 06:41:42

Basically I have a list of rows of data that has a column that I need to return the filter of this list where this column has the 2 highest values AND another certain value

I can, of course, do the

Select * from table where field = value

and

Select * from table where field >= (select max(field) from table where field not in (select max(field) from table))

(which filters by the top 2 values of the column)

Both of these work perfectly individually, though combining the 2 'where's' is not working?

Any help?!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-05 : 07:28:26

You can combine both clauses using a where:


Select * from table
WHERE
   field = value
   OR
   field >= (select max(field) from table where field not in (select max(field) from table))

Alternatively, you could use a UNION:

Select * from table 
   where field = value
UNION
Select * from table 
   where field >= (select max(field) from table where field not in (select max(field) from table))

Both of these will return only two rows if the row returned by field=value happens to be one of the top 2 rows. What do you want to happen in that case?

Also, there may be better (as in more readable and possibly even more efficient) ways of finding the top 2. For example:

SELECT TOP (2) * FROM TABLE ORDER BY field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 10:39:37

[code]
select *
from
(
Select *,row_Number over (order by field desc) as Rn
from table
)t
where Rn <=2
or field = value
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources