Results 1 to 3 of 3

Thread: DISTINCT Record

  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: DISTINCT Record

    I'm trying to solve the age old problem of trying to get distinct records from a query; now, most of you would say, just put the DISTINCT word in the SQL statement, however, this is what happens...

    ID EmployeeName No.ItemsSold
    01 Trista 3
    01 Pam 1
    02 Pam 4
    02 Calista 1

    As you can see, for each ID no, there are more than 1 employee sometimes...what i want is ..

    01 Trista 3
    02 Pam 4

    i wrote a function to do this;

    Dim temp as integer

    Public Function Convert(IDS As Integer) As Boolean
    If temp <> IDS Then
    temp = IDS
    Convert = True
    Else
    Convert = False
    End If
    End If

    End Function


    Then in the query, i put the function in , Convert([ID]) and it should return true; now, this does work but when i try to limit the query to all those that return true, it still gives me true and false ones...
    help?

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: DISTINCT Record

    Originally posted by Jerrie
    I'm trying to solve the age old problem of trying to get distinct records from a query; now, most of you would say, just put the DISTINCT word in the SQL statement, however, this is what happens...

    ID EmployeeName No.ItemsSold
    01 Trista 3
    01 Pam 1
    02 Pam 4
    02 Calista 1

    As you can see, for each ID no, there are more than 1 employee sometimes...what i want is ..

    01 Trista 3
    02 Pam 4

    i wrote a function to do this;

    Dim temp as integer

    Public Function Convert(IDS As Integer) As Boolean
    If temp <> IDS Then
    temp = IDS
    Convert = True
    Else
    Convert = False
    End If
    End If

    End Function


    Then in the query, i put the function in , Convert([ID]) and it should return true; now, this does work but when i try to limit the query to all those that return true, it still gives me true and false ones...
    help?
    What is the SQL statement that you are using to produce these records?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    I see your problem here -

    Looks like you want the ID and Name of the person who sold the most - Grouped by ID.

    You can use this query below to get the results you are looking for - its not the cleanest or best logic, but in this case it will work.

    Code:
    select a.id, a.name,a.sold
    from my_table a,
    (select id, max(sold) as most_sold
    from my_table
    group by id) b
    where a.id = b.id 
    and a.sold = b.most_sold
    May want to look into re-doing the table structure a little - its unclear whether the ID is the product ID or person ID - you should also think about using the person ID instead of the name in that table in case you have 2 employees with the same name!

    Hope this helps a little.

Posting Permissions

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