Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    11

    Unanswered: Extract Unique Values

    I am stumped and need help!!!

    I have the following SQL definition:

    SELECT DISTINCT tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Date_Checked_In, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original
    FROM tblFile_Management_Received;

    The results would show as:

    ClaimNumber Date Checked In Checked In By Original or Copy
    111111111 2/16/2005 8:34:24 AM XXXX Yes
    111111111 2/16/2005 1:23:19 PM XXXX Yes
    222222222 2/16/2005 8:34:52 AM XXXX Yes
    222222222 2/16/2005 1:24:49 PM XXXX Yes

    I am trying to use "Distinct" to show the following records when the query is ran (the desired results would be each unique claim number based on the earliest check in date):

    ClaimNumber Date Checked In Checked In By Original or Copy
    111111111 2/16/2005 8:34:24 AM BRQ7 Yes
    222222222 2/16/2005 8:34:52 AM BRQ7 Yes

    Can anyone help me with this??

    Any help is greatly appreciated.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    you then cannot use distinct

    Good news is you can use Group by

    SELECT tblFile_Management_Received.Claim_Number, min(tblFile_Management_Received.Date_Checked_In), tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original
    FROM tblFile_Management_Received
    Group by tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original;

    Greetz

  3. #3
    Join Date
    Oct 2004
    Posts
    11
    namliam,

    You are a life saver. I have been trying to figure this out for several hours and was banging my head against the wall.

    Thank you very very much

    P.S. Why couldn't I use DISTINCT????
    Last edited by ilfan83; 02-17-05 at 10:01.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    because
    111111111 2/16/2005 8:34:24 AM XXXX Yes
    111111111 2/16/2005 1:23:19 PM XXXX Yes
    are DISTINCT ...they contain different times

    izy
    currently using SS 2008R2

Posting Permissions

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