Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Nested filtering?

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    you want latest record per group, see this article

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    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

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by oldnickj
    Any help would be greatly appreciated
    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

    Quote Originally Posted by oldnickj
    WHAT am I doing wrong in the first query?
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    ... 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 are describing the syntax of standard SQL

    mysql, as you might know, has relaxed this requirement

    syntactically, this query is perfectly acceptable --
    Code:
    SELECT cba_id
         , dues_paid_id
         , MAX(dues_year)
      FROM dues_paid 
    GROUP 
        BY cba_id
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    you are describing the syntax of standard SQL

    mysql, as you might know, has relaxed this requirement
    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.

    Quote Originally Posted by r937
    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.

    Quote Originally Posted by r937
    the solutions have been provided by both bklr and guelphdad
    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Jan 2009
    Posts
    124
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    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
    you're quite right, i do know there are, and yes, they sure do

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj
    Thanks to all of you, not only the solution but very helpful discussion as to the origins of the problem.
    another example of this very same problem surfaced again today -- http://www.dbforums.com/ansi-sql/163...esult-one.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2009
    Posts
    124

    Faster nested query

    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

  12. #12
    Join Date
    Mar 2004
    Posts
    480
    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.

  13. #13
    Join Date
    Jan 2009
    Posts
    124
    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;

  14. #14
    Join Date
    Mar 2004
    Posts
    480
    Code:
    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!
    classic cross join here creating 21,000 * 3,000 rows or 61,000,000 rows to test.

    Use this instead
    Code:
    SELECT c.CBA_ID, c.LNAME, c.FN_MI
    FROM CBA c
    LEFT OUTER JOIN
     DUES_PAID d
    ON c.CBA_ID = d.CBA_ID
    AND d.CBA_ID IS NULL

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT c.CBA_ID, c.LNAME, c.FN_MI
    FROM CBA c
    LEFT OUTER JOIN
     DUES_PAID d
    ON c.CBA_ID = d.CBA_ID
    WHERE d.CBA_ID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •