Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Filtering records based on columns

    Hi Guys,

    How to i filter out records in a certain query. for an example, lets say i have
    this query

    Code:
    select id,firstname, age from student
    assume the results returned is

    HTML Code:
    Id      FirstName            Age
    ---------------------------
    1       Lily                     20
    2       Mary                  20
    3       Lily                     20

    so how do i change my query to only select those records without
    duplicate FirstName and Age. I cant use 'distinct' since it will return same
    results because of the id.

    Meaning the query that i wish to constuct would give me the following results
    HTML Code:
    Id      FirstName            Age
    ---------------------------
    1       Lily                     20
    2       Mary                  20
    Thank you very much for any help..

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    select min(id), firstname, age
    from student
    group by firstname, age

  3. #3
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by shammat
    Code:
    select min(id), firstname, age
    from student
    group by firstname, age
    thanks shammat, i will try it out

  4. #4
    Join Date
    Feb 2005
    Posts
    116
    hi,

    the script posted earlier does not seem to work in my situation,
    since i have an order by clause..

    the actual query that i have is something like this

    select id, firstname, age
    from student
    order by id, firstname, age

    If i change it to something below

    select min(id), firstname, age
    from student
    group by firstname,age
    order by id

    im getting some error,
    ORA-00979: not a GROUP BY expression

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You'll have to use
    ORDER BY min(id)
    or
    ORDER BY 1

    But basically the ORDER BY is not needed as the result will be order due to the usage of the GROUP BY

Posting Permissions

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