Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Unanswered: Help with group by order by problem

    I have a complex database and I am having problems with the group by function. Here is my query without the group by and the results:

    query:
    SELECT
    DATA.PROPOSAL_NUMBER,
    DATA.UNITERM_NUMBER,
    FACULTY.FIRST_NAME,
    FACULTY.LAST_NAME
    FROM
    EU.DOCUMENT_DATA DATA,
    EU.DOCUMENT_LINK_FACULTY LINK,
    EU.FACULTY FACULTY
    WHERE
    DATA.PROPOSAL_NUMBER = LINK.PROPOSAL_NUMBER AND
    LINK.MAIN_PI = '1' AND
    LINK.SSN = FACULTY.SSN AND
    EU.DATA.UNITERM_NUMBER='12987'
    ORDER BY
    DATA.UNITERM_NUMBER DESC,
    DATA.PROPOSAL_NUMBER DESC


    RESULTS:
    +-----------------+----------------+------------+-----------+
    | PROPOSAL_NUMBER | UNITERM_NUMBER | FIRST_NAME | LAST_NAME |
    +-----------------+----------------+------------+-----------+
    | 12098 | 12987 | John | Doe |
    | 12097 | 12987 | John | Doe |
    | 12096 | 12987 | John | Doe |
    | 11534 | 12987 | John | Doe |
    | 11199 | 12987 | John | Doe |
    | 11144 | 12987 | John | Doe |
    | 10614 | 12987 | John | Doe |
    | 10258 | 12987 | Jan | Doe |
    | 10257 | 12987 | Jan | Doe |
    | 9977 | 12987 | John | Doe |
    +-----------------+----------------+------------+-----------+

    As you can see the newest faculty member is John Doe on this record. Now our uniterm numbers are unique records and I do not want to display all of them, just one, so I use the group by feature.

    SELECT
    DATA.PROPOSAL_NUMBER,
    DATA.UNITERM_NUMBER,
    FACULTY.FIRST_NAME,
    FACULTY.LAST_NAME
    FROM
    EU.DOCUMENT_DATA DATA,
    EU.DOCUMENT_LINK_FACULTY LINK,
    EU.FACULTY FACULTY
    WHERE
    DATA.PROPOSAL_NUMBER = LINK.PROPOSAL_NUMBER AND
    LINK.MAIN_PI = '1' AND
    LINK.SSN = FACULTY.SSN AND
    EU.DATA.UNITERM_NUMBER='12987'
    GROUP BY DATA.UNITERM_NUMBER
    ORDER BY
    DATA.UNITERM_NUMBER DESC,
    DATA.PROPOSAL_NUMBER DESC

    Here are the results
    +-----------------+----------------+------------+-----------+
    | PROPOSAL_NUMBER | UNITERM_NUMBER | FIRST_NAME | LAST_NAME |
    +-----------------+----------------+------------+-----------+
    | 10257 | 12987 | Jan | Doe |
    +-----------------+----------------+------------+-----------+

    How do I get the group by function to return the first record like the first query. I was hoping the results would be:
    +-----------------+----------------+------------+-----------+
    | PROPOSAL_NUMBER | UNITERM_NUMBER | FIRST_NAME | LAST_NAME |
    +-----------------+----------------+------------+-----------+
    | 12098 | 12987 | John | Doe |
    +-----------------+----------------+------------+-----------+

    If I need to explain any other items please let me know. I have spent the last 8 hours trying to find a solution and have yet to find one. So here I am at the forums Might a subquery help? I have never used a subquery before.

    Thanks in advance

    Tim Hibbard

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    somebody solved this problem over here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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