Select a column not in Group By clause

Question

Select a column not in Group By clause

dr_csharp

SSCommitted

Points: 1765
More actions
January 11, 2010 at 11:56 am

#215482

hi friends
seems a book table with columns : Id,Title,Subject,Price
i want to get the most expensive books in each Subject.how can i do this ?
i tried this :
[Code]
SelectTitle,Subject,Max(Price)
FROMBook
GROUP BYSubject
[/Code]
but it raise error that "Column 'Title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
what can i do ?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

This seriously looks like homework. Is it?

The way I'd do this is build a CTE that uses the Rank() function to rank the titles by price within each subject, then the outer query would filter by the rank column of the CTE.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

GSquared (1/11/2010)
This seriously looks like homework. Is it?
The way I'd do this is build a CTE that uses the Rank() function to rank the titles by price within each subject, then the outer query would filter by the rank column of the CTE.

its not home work actually, it emergency now cause my friend ask me.

i think about it but i raise error .. hum , could u please tell me more what do u mean ?

dr_csharp SSCommitted Points: 1765 More actions · Answer 3

hooRRRa ,i do like this :

SELECT * FROM Book Inner JOIN

(

SelectSubject,Max(Price) as Price

FROMBook

GROUP BYSubject

)T

On T.Subject=Book.Subject AND T.Price=Book.Price

but please guide me if there's any better solution :O)

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

Normal aggregate functions (SUM(), AVG, () COUNT(), MIN(), MAX(), etc.) are available to be used as windowed functions (using OVER and PARTITION BY).

SELECT Title,Subject, Max(Price) OVER(PARTITION BY Subject) As MaxPriceBySubject

FROM Book

-Eddie

Eddie Wuerch
MCM: SQL

dr_csharp SSCommitted Points: 1765 More actions · Answer 5

Eddie Wuerch (1/11/2010)
Normal aggregate functions (SUM(), AVG, () COUNT(), MIN(), MAX(), etc.) are available to be used as windowed functions (using OVER and PARTITION BY).
SELECT Title,Subject, Max(Price) OVER(PARTITION BY Subject) As MaxPriceBySubject
FROM Book
-Eddie

i do this exactlly, but this return all rows :O(

how should i change your code for geting most expencive book in per subject ?

Nabha SSCrazy Eights Points: 8534 More actions · Answer 6

The query that Gsquared has suggested, (untested)

;WITH CTE (Title, Subject, row_no) as

( SELECT Title,Subject, Row_number() OVER(PARTITION BY Subject

Order by price desc) as row_num

FROM Book)

Select * from CTE

where row_num = 1

---------------------------------------------------------------------------------

taseedorf SSC Eights! Points: 917 More actions · Answer 7

Sorry to be a troll, but....

It is emergency because my friend asked me...

NO...come on.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

taseedorf (12/9/2015)
Sorry to be a troll, but....
It is emergency because my friend asked me...
NO...come on.

Please note....this thread is 5 years old.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 9

Sean Lange (12/9/2015)
taseedorf (12/9/2015)
Sorry to be a troll, but....
It is emergency because my friend asked me...
NO...come on.
Please note....this thread is 5 years old.

Heh... I've never seen a 5 year old troll before. They're a lot shorter than what I expected. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)