In an organization dues paid table, we have a record for each time a member has paid for a years membership. Some members will have ten records. I want to have a distinct list of members with the most recent year they have paid for.

This query produces the following result which is wrong!

SELECT CBA_ID, DUES_PAID_ID, MAX(DUES_YEAR)
FROM DUES_PAID GROUP BY CBA_ID

"CBA_ID","DUES_PAID_ID","MAX(DUES_YEAR)"
NULL,"27545","2008"
"1","18185","2008"
"2","21476","2007"

Query 2 :
SELECT CBA_ID, DUES_PAID_ID, DUES_YEAR
FROM DUES_PAID
where CBA_ID=1

"CBA_ID","DUES_PAID_ID","DUES_YEAR"
"1","18185","1998"
"1","19892","2002"
"1","21475","1997"
"1","23104","1996"
"1","24625","1995"
"1","27867","2000"
"1","28983","2001"
"1","31099","2003"
"1","32786","2004"
"1","33681","2005"
"1","36369","2006"
"1","37998","2007"
"1","39322","2008"


As you can see #1, in the first query, is returning the wrong "DUES_PAID ID (18185) and it should show (39322)! WHAT am I doing wrong in the first query?

Any help would be greatly appreciated

Oldnickj

you want latest record per group, see this article

try this i am not sure it is correct for ur solution
select s.DUES_PAID_ID,t.CBA_ID,t.dues_year
from DUES_PAID s
inner join
(SELECT CBA_ID, MAX(DUES_YEAR) as dues_year
FROM DUES_PAID GROUP BY CBA_ID) t on t.dues_year = s.dues_year and t.cba_id = s.cba_id

It would probably help if you told us what all the fields mean but hey ho. I'd try something like :

Code:

SELECT CBA_ID, MAX(DUES_YEAR)
FROM   DUES_PAID 
GROUP  BY CBA_ID

It didn't work in your first attempt because when you group by a field, or fields, then only that field, or fields, can appear in the select list and any other references to fields must be using an aggregate function ie (max, count, avg etc). You had added the DUES_PAID_ID field for some reason.

Mike

you are describing the syntax of standard SQL

mysql, as you might know, has relaxed this requirement

syntactically, this query is perfectly acceptable --the problem, of course, is that semantically, the value of dues_paid_id that is returned is not necessarily the one that corresponds to the row which has the MAX(dues_year)

this is described here: The Rows Holding the Group-wise Maximum of a Certain Field

the solutions have been provided by both bklr and guelphdad

Most systems (such as MySQL, Sybase and MS SQL) allow the syntax but the output produced is never what the coder expects. This was why I stated what I did. Perhaps in time someone out there will find a use for what's generated but lord help the person who has to maintain that code.

The user wanted to find the "distinct list of members with the most recent year they have paid for". I believe the SQL I supplied did this and in the simplest way possible. The article you linked to covers a slightly different topic but I did note your contribution within it.

Thanks for your judgement Rudy but I think it's up to the user rather than yourself to make the call. Hopefully they'll be happy with any of the help given but, if not, at least I hope they won't have the bad grace to put people down in public. If I am wrong then I'll try and learn from the mistake. As I'm sure you know there are people who never recognise their own failings, they just figure they're always right, and end up sounding like pompous fools

If this question involved ANSI (or more accurately now ISO) standard SQL, I'd agree with Mike. Standard SQL only allows expressions in the SELECT list that are either listed in the GROUP BY clause or are enclosed within aggregate functions.

As this is a MySQL question, I have to agree with r937... I think that Guelphdad and bklr have provided the correct answers to the original question.

-PatP

Thanks to all of you, not only the solution but very helpful discussion as to the origins of the problem. I have a lot to learn!

Now I need to lear about the efficiencies of the various methods.

Nick

you're quite right, i do know there are, and yes, they sure do

another example of this very same problem surfaced again today -- Getting the maximum value in a join and displaying the result as one

This query works well but I'm interested if anyone can suggest a more efficient alternative. I limited the output to three years which was a huge improvement but is there more I can do?

Nick

SELECT s.DUES_PAID_ID, s.CBA_ID,s.DUES_YEAR, c.LNAME,c.FN_MI
FROM DUES_PAID s
LEFT JOIN CBA c ON s.CBA_ID = c.CBA_ID
INNER JOIN
(SELECT CBA_ID, MAX(DUES_YEAR) as dues_year
FROM DUES_PAID GROUP BY CBA_ID) t on t.dues_year = s.dues_year and t.cba_id = s.cba_id
WHERE s.DUES_YEAR IN ('2007','2008','2009') AND c.LNAME IS NOT NULL

why do a left join but then add the is not null constraint to the right table? You might as well do an inner join and then you don't have to test for not null since only matching rows would be returned.

It was an artifact from the original left join. The database itself is an artifact from my clients Access Database, which we are soon to detach. My other task, is to find build a query which will compare the CBA (members) table to the Dues_Paid table to list the members in the CBA who are not in the UDE-paid table. Something like:
SELECT c.CBA_ID, c.LNAME, c.FN_MI
FROM CBA c, DUES_PAID d
WHERE c.CBA_ID <> d.CBA_ID

However the Dues_Paid able has 21k records and the CBA table has 3k, the query is very slow to respond!
Here is the schema:
CREATE TABLE `CBA` (
`CBA_ID` int(11) NOT NULL auto_increment,
`access_usr` int(11) default NULL,
`randomkey_usr` varchar(100) default NULL,
`active_usr` int(25) default NULL,
`black_balled` varchar(4) default NULL,
`username_usr` varchar(100) default NULL,
`password_usr` varchar(100) default NULL,
`committee_usr` varchar(100) default NULL,
`s_Generation` int(11) default NULL,
`ID` int(11) default '0',
`s_GUID` text,
`LNAME` varchar(28) default NULL,
`s_Lineage` text,
`SN` int(6) default NULL,
`FN_MI` varchar(22) default NULL,
`TITLE` varchar(9) default NULL,
`SX` varchar(10) default NULL,
`DJ` varchar(250) default NULL,
`FN_SP` varchar(30) default NULL,
`SPNAME` varchar(30) default NULL,
`SPLNAME` varchar(50) default NULL,
`ADD1` varchar(50) default NULL,
`ADD2` varchar(50) default NULL,
`City` varchar(30) default NULL,
`ST` varchar(25) default NULL,
`ZIP` varchar(18) default NULL,
`ADD3` varchar(30) default NULL,
`ADD4` varchar(255) default NULL,
`CITY2` varchar(255) default NULL,
`ST2` varchar(255) default NULL,
`ZIP2` varchar(255) default NULL,
`country` varchar(100) default NULL,
`PHONE1` varchar(255) default NULL,
`PHONE2` varchar(255) default NULL,
`SEARCH_KEY` varchar(255) default NULL,
`EMail` varchar(255) default NULL,
`PR_LABEL` varchar(255) default NULL,
`INFO` varchar(255) default NULL,
`ROLE` varchar(255) default NULL,
`NOTE1` varchar(255) default NULL,
`NOTE2` varchar(255) default NULL,
`NOTE3` varchar(50) default NULL,
`OWNER_1ST` varchar(255) default NULL,
`2_2` varchar(255) default NULL,
`BN1` varchar(255) default NULL,
`SB1` varchar(255) default 'N',
`PORT1` varchar(255) default NULL,
`XNAME1` varchar(255) default NULL,
`FO1` varchar(255) default NULL,
`DS1` varchar(255) default NULL,
`YR1` varchar(255) default NULL,
`BLDTYP1` varchar(255) default NULL,
`BLDLOC1` varchar(255) default NULL,
`L1` varchar(255) default NULL,
`B1` varchar(255) default NULL,
`SA1` varchar(255) default NULL,
`DD1` varchar(255) default NULL,
`1_1` int(11) default NULL,
`HR1` varchar(255) default NULL,
`SOLD1` varchar(50) default NULL,
`UPDATED` date default NULL,
`cdate` datetime default NULL,
`edate` date default NULL,
`PRINT` varchar(255) default 'Y',
`DUESPD99` varchar(255) default NULL,
`DUESPD98` date default '1998-01-01',
`DUESPD97` varchar(4) default 'N',
`DUESPD96` varchar(4) default NULL,
`DUESPD95` varchar(255) default NULL,
`MBR_DIED` varchar(255) default NULL,
`SP_DIED` varchar(50) default NULL,
`BN2` varchar(255) default NULL,
`PORT2` varchar(255) default NULL,
`XNAME2` varchar(255) default NULL,
`FO2` varchar(255) default NULL,
`DS2` varchar(255) default NULL,
`SB2` varchar(255) default NULL,
`YR2` varchar(255) default NULL,
`BTYP2` varchar(255) default NULL,
`BLDLOC2` varchar(255) default NULL,
`L2` varchar(255) default NULL,
`B2` varchar(255) default NULL,
`SA2` varchar(255) default NULL,
`DD2` varchar(255) default NULL,
`1_2` int(11) default NULL,
`HR2` varchar(255) default NULL,
`SOLD2` varchar(50) default NULL,
`BN3` varchar(255) default NULL,
`SB3` varchar(255) default NULL,
`PORT3` varchar(255) default NULL,
`XNAME3` varchar(255) default NULL,
`FO3` varchar(255) default NULL,
`DS3` varchar(255) default NULL,
`YR3` varchar(255) default NULL,
`BLDTYP3` varchar(255) default NULL,
`BLDLOC3` varchar(255) default NULL,
`L3` varchar(255) default NULL,
`B3` varchar(255) default NULL,
`SA3` varchar(255) default NULL,
`1_3` int(11) default '0',
`DD3` varchar(255) default NULL,
`HR3` varchar(255) default NULL,
`SOLD3` varchar(50) default NULL,
`BILL` bigint(1) unsigned default '0',
`WINTER` bigint(1) unsigned default '0',
`SPRING` bigint(1) unsigned default '0',
`FALL` bigint(1) unsigned default '0',
`RESIGNED` varchar(50) default NULL,
`Foreign` bigint(1) unsigned default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`sent_live` int(5) unsigned default '1',
`Secure_password_usr` varchar(100) default NULL,
`Atempdate_usr` datetime default NULL,
`Attemps_usr` int(11) default NULL,
`middle_init_usr` varchar(11) default NULL,
PRIMARY KEY (`CBA_ID`),
KEY `CBA_ID` (`CBA_ID`),
KEY `CBAID` (`ID`),
KEY `CBALNAME` (`LNAME`),
KEY `CBASN` (`SN`),
KEY `s_Generation` (`s_Generation`),
KEY `SEARCH KEY` (`SEARCH_KEY`),
KEY `UPDATED` (`UPDATED`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table DUES_PAID
# ------------------------------------------------------------

CREATE TABLE `DUES_PAID` (
`DUES_PAID_ID` int(11) NOT NULL auto_increment,
`CBA_ID` int(10) default NULL,
`CK_NBR` int(10) default NULL,
`BANK_NBR` varchar(50) default NULL,
`AMT_PAID` decimal(19,4) default NULL,
`DATE_OF_DEP` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`DUES_YEAR` int(10) default NULL,
`Donation_Comments` varchar(50) default NULL,
PRIMARY KEY (`DUES_PAID_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

classic cross join here creating 21,000 * 3,000 rows or 61,000,000 rows to test.

Use this instead