Selecting all tuples with maximum value for an attribute

[kartik14]

Hi,

I have a derived attribute in my table and I'm sorting the table in the descending order of this attribute. My query should retrieve all the tuples which have the maximum value for this derived attribute.

If I use SELECT TOP 1, I'm only getting one of the required results. How can I get all the tuples with the maximum value ??

Thanks in advance..

EDIT*: The only problem I'm having in implementing this query is not being able to refer to the derived attribute in the WHERE clause. Can anyone tell me how it's done ?

[dickersonka]

select * from (select * from table t1 where t1.column=value) as t2
where t2.column=value

I'm not sure if this will help your case but from what you are asking it sounds like you want to group records based on an attribute and determine the maximum value of each attribute.

If you have a table like the one in this definition

CREATE TABLE dbo.StateAndCountyPopulation
(
     CountyID int NOT NULL,
     CountyName varchar(50) NOT NULL,
     State char(2) NOT NULL,
     CountyPopulation INT NOT NULL
)

So if you wanted to see a list of States and their largest County population, you would have to group by the State code and use the aggregate function MAX to determine the highest value of County Population in the code example below.

SELECT
     State,
     MAX(CountyPopulation)
FROM dbo.StateAndCountyPopulation
GROUP BY State

Hope that helps