SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS Access Query Help

    Hi, i have the following tables:

    Code:
    Employees Table
    ---------------
    Code:
    Employee_ID F_Name	  L_Name
    1		   John		Smith
    2		   George	  Carlin
    3		   Joseph	  Johnson
    
    Ratings Table
    -------------
    Review_ID   Employee_ID Review_Date Rating
    1		   1		   01/03/00	4
    2		   1		   01/08/01	6
    3		   1		   12/08/03	9
    4		   1		   04/09/05	8
    5		   2		   04/08/99	9
    6		   2		   03/08/88	8
    7		   2		   05/09/03	4
    8		   3		   06/09/99	4
    9		   3		   04/02/01	8
    10		  3		   01/03/98	4
    11		  3		   04/05/03	2
     

    What I want to do is to select and dipslay the latest review for each employee (the one with the most recent date). The query should return this table:

    Code:
     Wanted Results
    --------------
    Employee_ID F_Name  L_Name  Review_ID   Review_Date Rating
    1		   John	Smith   4		   04/09/05	8
    2		   George  Carlin  7		   05/09/03	4
    3		   Joseph  Johnson 11		  04/05/03	2
    What is the sql code that I schould use?
    Thanks in advanced for your help!
    Last edited by compwizard; Jun 15, 2003 at 17:44.
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Here's how I would do it:
    Code:
    SELECT Employee_ID, F_Name, L_Name, Review_ID, Review_Date, Rating
    FROM Employees E, Ratings R
    WHERE E.Employee_ID = R.Employee_ID
    AND Review_Date = (SELECT MAX(Review_Date) FROM Ratings WHERE Employee_ID = E.Employee_ID)
    Now if this is MySQL, then you'll need to do it another way since it doesn't support Sub-queries
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright..I will try that tonight..thank you for your help.
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  4. #4
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell
    Now if this is MySQL, then you'll need to do it another way since it doesn't support Sub-queries
    Quite right to check, but wasn't the thread title - which actually *IS* descriptive for once (something to be encouraged) - clear enough?!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by M@rco
    Quite right to check, but wasn't the thread title - which actually *IS* descriptive for once (something to be encouraged) - clear enough?!! [img]images/smilies/tongue.gif[/img] [img]images/smilies/FRlol.gif[/img] [img]images/smilies/wink.gif[/img]
    Busted....who reads thread titles anyways...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could also do something like this...

    Code:
    SELECT
    	E.Employee_ID,
    	E.F_Name,
    	E.L_Name,
    	R.Review_ID,
    	R.Review_Date,
    	R.Rating
    FROM
    	(
    	SELECT
    		Employee_ID,
    		Max(Review_Date) AS Max_Review_Date
    	FROM Ratings
    	GROUP BY Employee_ID
    	) AS Q,
    	Employees AS E,
    	Ratings AS R
    WHERE
    	Q.Employee_ID = E.Employee_ID
    AND
    	Q.Max_Review_Date = R.Review_Date
    now i guess you just need to ask yourself which is going to be faster: an extra sub-select for every unique Employee_ID who has writen a review, or joining two tables and a simple view.

    note: i joined Q to R using the Review_Date field. this could cause some problems if any of your reviews are writen on the same date. im not sure how you could get around that with my query. originaly, i tried writing the view in my query so that it included a Review_ID field, but access wouldnt let me do that. perhaps someone else will have a suggestion on how to fix this.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a suggestion on how to fix what? having more than one row with the max value is not a problem, it is a fact of life -- i would be dismayed at any solution that tried to "fix" (i.e. hide) rows with ties

  8. #8
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my point was that, because it joins on the date, which might not be unique, it might join the wrong rating. iow, it might join a rating which does not match the correct Review_ID.

    i was hoping someone could find a way to make something like this work...

    Code:
    SELECT
    	Review_ID,
    	Employee_ID,
    	Max(Review_Date) AS Max_Review_Date
    FROM
    	Ratings
    GROUP BY
    	Employee_ID


Bookmarks

Posting Permissions

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