Re: SQL Question, Aggregate and nonAggregate fields..

Apparantly, my first post didn't make it out, so let's try it again. You can't do what you want to do with a simple aggregate. You need to use an aggregate within a correlated sub-query to restrict the rows returned by the main query. I don't have a system handy to test it or a manual to verify the syntax, but something along the lines of the following should work for what you want to do: select publish_date, series_id, article_id from dtv_articles a where publish_date = (select max(publish_date) from dtv_articles where a.series_id = dtv_articles.series_id); The sub-query is correlated by series id and returns to the main select the max(publish_date), which you can then use to restrict the rows returned in the main query. This should give you all articles published on the max date by series, presumably one per series -- possibly more depending on the data. You make no mention of whether the table has a unique index on these three columns. You may have to tinker with this, but it should give you what you want. Another possibility is to use two selects, the first one into a temporary table which would hold the series and max(publish_date). You can then join back to this table by series and publish date to extract the articles you are interested in. Again, the possibility of multiple rows exists depending on what is contained in the original table. Don't forget to add 'with no log' to the select into the temporary table if you are working in a logged database, especially if the table is large, or you stand a possibility of running into a long transaction. It makes no sense to log a temporary table anyway, since it goes away as soon as your process ends. John Windberg wrote: > SQL Question > > Aggregate and non-aggregate fields. > > I have the table dtv_articles... > > Article_id (unique primary key integer) > series_id (integer) > publish_date date > etc... > > I want the article_id that would be the latest article for each group of > series... > > If I do > > select max(publish_date), series_id from dtv_articles group by series_id > order by series_id; > > I get exactly what I want, but not the article_id. > > If I add: > > select max(publish_date), series_id, article_id from dtv_articles group > by series_id order by series_id; > > I have to group by article_id, which no longer gives me the > max(publish_date) per series, but per series and article_id... > > How do I get the fields that are not part of the groups??? > > Thanks > > -John > > -- > John B. Windberg > Database Administrator > Imagine Media Inc. > jonwa@imaginemedia.com