I'm new to SQL programming and was hoping someone here could tell me where I'm going wrong with the following SQL statement as part of my stored procedure for a complex search engine. I'd appreciate it if you could spare a few mins.


I have a database table called tblMedia and within the table there can X number of rows (a transaction, ie Media on loan) against one Media item (primary key medno_id). Using the query below I want to be able to search for e.g. media item "50" and remove all duplicate rows showing only the row with the most recent date (med_effdate) of transaction.


SELECT tblMedia.medno_id, MAX(tblMedia.med_effdate)
FROM tblMedia
WHERE tblMedia.medno_id = 50
GROUP BY tblMedia.medno_id
ORDER by tblMedia.medno_id


The above query works perfect but displays limited info, I want to be able select more columns but everytime I try add the column names to the SELECT statement it errors. This is what I have tried and the errors I receive:


SELECT tblMedia.medno_id, MAX(tblMedia.med_effdate), tblMedia.class_id, tblMedia.unit_id, tblmedia.med_title
FROM tblMedia
WHERE tblMedia.medno_id = 50
GROUP BY tblMedia.medno_id
ORDER by tblMedia.medno_id


Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblMedia.class_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblMedia.unit_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblMedia.med_title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Not too sure where I'm going wrong or what I need to do, I've been searching the web all week but no luck, would appreciate any help
Thanks
Chris

I think it is caused by the GROUP BY section - group by is for summarising groups of things, which means you give a column to group by (medno_id), and then a way to combine the other columns, such as MAX(med_effdate). Here MAX is an aggregate function since it is 'aggregating' the dates into one value - the maximum. Because class_id and unit_id etc appear on their own, the database doesn't know how to combine the values (even though they are all probably the same).

I suggest this - select all the columns as normal without the MAX(), that will get you all the records with the right medno_id. Then use ORDER BY med_effdate DESC to sort by date, and put the most recent one at the top. Then you can use LIMIT 1 to only take the first result. This should get you the right result without the errors.

So in full:

SELECT tblMedia.medno_id, tblMedia.med_effdate, tblMedia.class_id, tblMedia.unit_id, tblmedia.med_title
FROM tblMedia
WHERE tblMedia.medno_id = 50
ORDER by tblMedia.med_effdate DESC
LIMIT 1

Without knowing the exact database system you are using, I can't be sure of the syntax, but something close to this should work.

Thanks Oberon! really do appreciate that, I'll save it in notepad for future reference

It looks like it does exactly what I want, although as my colleagues keep saying "more than a thousand ways to skin a cat" .. I managed to saught help of a very experienced SQL programmer who suggested I do the following:

SELECT a.medno_id, a.med_effdate, a.class_id, a.unit_id, a.med_title
FROM tblMedia a
WHERE a.med_effdate = (SELECT MAX(b.med_effdate) FROM tblMedia b WHERE a.medno_id = b.medno_id)
AND a.medno_id = 50
ORDER by a.medno_id

I now understand how to do this using the two methods but thanks to you I now know the syntax LIMIT exists, something I didn't know! very useful.

Thanks
Chris