MS SQL

MS SQL RSS

Selecting all tuples with maximum value for an attribute

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Nov 2007

Posts: 27

Reputation: kartik14 is an unknown quantity at this point

Solved Threads: 0

kartik14

Offline

Light Poster

Selecting all tuples with maximum value for an attribute

Oct 12th, 2008

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.

Copy from one, its Plagiarism; Copy from many, its Research

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Selecting all tuples with maximum value for an attribute

Oct 21st, 2008

 Help with Code Tags 
 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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Selecting all tuples with maximum value for an attribute

Oct 25th, 2008

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

 Help with Code Tags 
 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.

 Help with Code Tags 
 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