Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Question Unanswered: How to return only one record per id

    I have a database that contains multiple entries per client and I only want to select/read the last one record for each client. I know how to eliminate the dups in the detail section while printing but I actually want to do this while reading the records. I have been unable to do this in the record selection. Select distinct will not work as the records for each client will have different dates and other information. Can any one tell me how this can be accomplished?

    I am running Crystal 10. Thanks in advance for any help offered.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What database are you using?

    P.S. please do not cross-post your questions
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    Sorry, I cross posted as I would like to know how to do this in Crystal and in SQL.


    I am using InterSystem's Cache Database

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Now you have to define what you mean by "one entry per client"
    If a client has 3 records how do you decide which one you want to show?

    Perhaps some sample data would help?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2007
    Posts
    18
    If the client has more than one record, I wish to get the the last one.
    I am not an SQl programmmer or crystal. My expertise is in IBM assembler and Fortran. I have coded the below SQL statement in a Crystal Command and appear to be getting some favorable results with it.

    SELECT admission_data.PATID admission_data.military_branch_value, MAX(admission_data.EPISODE_NUMBER) AS LAST_EP_NUMBER
    FROM admission_data admission_data
    WHERE admission_data.FACILITY='4'
    GROUP BY admission_data.PATID

    If you know of any more ways or more efficient methods, I would appreciate your sharing with me. Thanks for you help

Posting Permissions

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