Results 1 to 8 of 8
  1. #1
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17

    rank by dates

    Hello, I have the following case:

    subject id dtvisit rank_dtvisit
    1 1/2/2003
    1 2/3/2006
    1 2/4/2007
    1 3/9/2011
    1 9/12/2019
    2 5/6/2018
    2 6/2/2018
    2 12/12/2018
    3 12/11/2017
    3 12/22/2018



    is it possible to rank the dates in a way to obtain (for subject 1, for example) the earliest date ranked as 1 and the latest ranked as 5... and so on for all other subjects. Thank you very much for your help.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17
    Thanks, these works if I have single values I want to rank, like Customers (CustomerID in the query), but the question is a bit different, as you can see: I have Subject_1 that has different dates (dtvisits), Subject_2 that also has different dates (dtvisit)... etc. Now I want to be able to rank those dates (dtvisits) within the Subject_1, Subject_2, Subject_3 etc, so later i can filter the date of first visit (rank 1), the date of the second visit (rank 2) for each of these Subjects. I hope I was clear enough in my explanation. Thanks.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ranking query:

    Code:
    SELECT 
           t1.SubjectId,
           t1.Dtvisit,       
           COUNT(*) AS Rank
        FROM
           tblBatta AS t1
         
           LEFT JOIN tblBatta AS t2
              ON t1.SubjectID= t2.SubjectID AND
                 t1.DtVisit>= t2.DtVisit
        GROUP BY
           t1.SubjectID,
           t1.Dtvisit
    SubjectId Dtvisit Rank
    1 02-Jan-03 1
    1 03-Feb-06 2
    1 04-Feb-07 3
    1 09-Mar-11 4
    1 12-Sep-19 5
    2 06-May-18 1
    2 02-Jun-18 2
    2 12-Dec-18 3
    3 11-Dec-17 1
    3 22-Dec-18 2

  5. #5
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17
    That's it! Thank you very much for your time and your help. Cheers!

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17

    Works!

    Hello Orange,

    I've modified your code and it works perfectly for me.
    I would really appreciate a bit of your time (if you have it) to explain to me what this code actually does, I mean why is necessary to duplicate the table etc.
    Is there any other similar cases when this is necessary to be done like this.

    Thanks a lot.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It is necessary to use the same table twice in this structure. This SQL is making a left join between 2 copies of tblBatta.
    I created an Alias for each table , T1 and T2. T1 could be removed and just refer to tblBatta.
    The alias T2 is used to distinguish which of the 2 tables certain fields or criteria refer to.

    In this case, the criteria are that the SubjectID in each table are equal AND
    the DtVisit in T1 is greater or equal (>=) to the dtVisit in T2.
    Code:
     ON t1.SubjectID= t2.SubjectID AND
                 t1.DtVisit>= t2.DtVisit
    Review this link (Subqueries) by Allen Browne to see more details for use of ALIAS.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  2. Rank Query
    By cbritt927 in forum Queries
    Replies: 2
    Last Post: 06-02-2015, 04:42 PM
  3. Rank or Primary Key?
    By bigmatt911 in forum Reports
    Replies: 4
    Last Post: 01-03-2014, 05:37 PM
  4. please need help RANK
    By Andyjones in forum Queries
    Replies: 8
    Last Post: 09-10-2011, 12:12 AM
  5. Rank Function
    By Dongola in forum Programming
    Replies: 1
    Last Post: 05-07-2011, 06:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums