Selecting all tuples with maximum value for an attribute

Expand Post »

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 ?

Last edited by kartik14; Oct 12th, 2008 at 2:24 am.

kartik14

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Nov 2007

Permalink

Oct 21st, 2008

Re: Selecting all tuples with maximum value for an attribute

MS SQL Syntax (Toggle Plain Text)
SELECT * FROM (SELECT * FROM TABLE t1 WHERE t1.COLUMN=value) AS t2
WHERE t2.COLUMN=value
select * from (select * from table t1 where t1.column=value) as t2
where t2.column=value

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Oct 25th, 2008

Re: Selecting all tuples with maximum value for an attribute

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

MS SQL Syntax (Toggle Plain Text)
CREATE TABLE dbo.StateAndCountyPopulation
(
     CountyID int NOT NULL,
     CountyName varchar(50) NOT NULL,
     State char(2) NOT NULL,
     CountyPopulation INT NOT NULL
)
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.

MS SQL Syntax (Toggle Plain Text)
SELECT
     State,
     MAX(CountyPopulation)
FROM dbo.StateAndCountyPopulation
GROUP BY State
SELECT
     State,
     MAX(CountyPopulation)
FROM dbo.StateAndCountyPopulation
GROUP BY State

Hope that helps

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Multiple object records forming one query

Next Thread in MS SQL Forum Timeline: Schema vs. Role Security

DaniWeb Message

Cancel Changes

User Name
Password