February 11, 2008 at 1:14 pm
i have a table (auto) that has multiple columns but the two i'm concerned with are
car_model
year
the query i'm trying to build is to find the most recent year make for each model. This would be easy but there are some models that have been discontinued so not all have a 2008 year.
How do i find the max year for each car_model all in the same query? here's how i do it for each model
Select car_model,year from table auto where car_model ='cherokee' and year =(select max(year) from table auto where car_model ='cherokee')
the result is
cherokee,2001
February 11, 2008 at 5:05 pm
I'll give you a hint.
USE GROUP BY
-SQLBill
February 12, 2008 at 6:41 am
thanks for the point in the right direction i'll update here once i get the query working
nick
February 12, 2008 at 8:44 am
thanks sqlbill,
Select car_model,max(year) from table auto group by car_model
here is what i came up with and it works great. one last question if i added a column to this query say exterior color would the group by still apply ?
February 12, 2008 at 3:39 pm
Yes. Just need to add the new column to the GROUP BY.
-SQLBill
February 22, 2008 at 8:32 am
yes that was what i saw in the tutorial, but the issue i face is when you add color to the group by it no longer respects my max year so i get all the colors and year versions for those. Is there a way around this? Should i create a look up table and do a join?
February 22, 2008 at 9:42 am
Nick,
Just so we're all clear and on the same page, can you post some sample data and give an example of the result set that you would like to get based off that sample data? It makes it much easier to give you some guidence.
February 22, 2008 at 12:15 pm
sure and thanks for viewing,
my original query
Select car_model,max(year) from table auto where car_model='cherokee' group by car_model
results
cherokee 2001
heres what i get when i add ext_color
Select car_model,max(year),ext_color from table auto where car_model='cherokee' group by car_model,ext_color
results
cherokee 2001 Black
cherokee 2001 Red
cherokee 2001 Forest Green
cherokee 2001 Blue Pearlcoat
cherokee 2001 Sienna Pearlcoat
cherokee 2001 Silverstone Metallic
cherokee 2001 Stone White
cherokee 2000 burgandy
cherokee 1998 brown
etc..
what i want is cherokee 2001 and colors once this works i plan to remove the where claus i just have that in for testing.
so here is my desired result set
cherokee 2001 Black
cherokee 2001 Red
cherokee 2001 Forest Green
cherokee 2001 Blue Pearlcoat
cherokee 2001 Sienna Pearlcoat
cherokee 2001 Silverstone Metallic
cherokee 2001 Stone White
thank you for your time
February 22, 2008 at 12:32 pm
OK, I think I've almost got it. There's still some missing pieces though. You shown us the result set that you are after, but we need to know what the source data looks like as well. I see that you want to find the newest year for each model. This part is simple, as you've already done, use the MAX() function and group by that model. You've thrown a twist in now that you want to also diplay the color. I am assuming that you want to display only the available colors for the latest year, correct?
It would be a great help if you could post your DDL for the Auto table so we can see that column defs and any primary key or unique constraints that you've defined. Also, as I've stated above, source data will also be a great help. Knowing how you want the data to look after the query is only half of the picture. Once we have both, this should be a simple solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply