Selecting multiple maximum values?? 1

[Lukasade]

Is it possible to get the maximim value (for example an age) for every different value (eg surname) in another field of the record??

For example the oldest person for every surname present in a surname column?

I've tried subqueries, distinct, etc and I cant get it.

 

[AlexCuse]

You need to take the max age, and group by surname. Something like this:

select surname
, max(age)
from leTable
group by surname

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Lukasade]

Yep that worked! However when I wanted to add fields from another table, I get "You tried to execute a query that does not include the specified expression.. as part of an aggregate function"

Ive made heaps of large databases and the SQL works fine with aggregates and multiple tables, but never had this problem before. When the query does work and Ive filled in all the GROUP BY [other fields], I get every possible combination. I believe the term I researched up was Cartesian join (too much output), is this because I am using 3 tables for getting the fields??

 

[AlexCuse]

Anything that you are not taking the MAX of needs to be included in your group by (or another aggregate function like Min, Avg, Sum, etc..)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

What is your actual SQL code ?
A common way is to join the aggregate select instruction as a subquery.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lukasade]

Ok, sorry, different timezones means a delayed response. I have 3 tables, well 4 but ones not needed:

borrower(id, first_name, surname, age)
DVD(id, title, rating, limit)
DVDloan(first_name, surname, rating)

(by the way its my own little DVD library system)

What I need to list is:

id, surname, first_name, rating, limit, age(max age for every surname [don't as me why, thats somebody elses idea]

You dont need to know the purpose of each field do you?? I don't think I thought the structure out that well..

 

[Lukasade]

wait... sorry, limit is supposed to go in the DVDloan table

 

[AlexCuse]

You will want to join to the query listed above from whatever query gets your list of people. I don't see why you want the max(age) by Surname listed next to each person with Surname x though.

Is this a school project?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Lukasade]

Thanks, no its not a school project (Yes I did read the Posting Policies!)

Im just creating a database for a friend who tends to misplace some of his DVDs cuz he cant keep track of them.

In regards to the MAX, I just used the Age because I couldnt be botheredn writing the other table which has the info such as genre, running time etc, that is what I want to find out, the MAX running time per Genre, and I was lazy at the time so I just substituted it for age.

 

[AlexCuse]

Ah, I see.

Do you understand what I mean by joining to the subquery?

Here is an example (made up my own table names, assumes a 'genreID' stored with each film, and a genre table containing genreID, genreName)

select a.FilmName
, b.FilmGenre
, a.RunningLength
, b.RunningLength as MaxLengthForGenre
from Film a
inner join
(
	select z.genreID
	, z.genreName as FilmGenre
	, max(x.RunningLength)
	from Genre z
	inner join Film x
	on z.genreID = x.genreID
	group by z.genreID, z.genreName
) b
on a.genreID = b.genreID

Good Luck with your project

Alex

Ignorance of certain subjects is a great part of wisdom