SQL query help needed

Help trying to extract data from table where only some rows are required

[MentalDev]

Ok I've got data in a table that's not that easy to extract, probably easier to show you...

ID, Record, Group, Item, ErrorMessage
1, 99, Person, FName, "FName contains an invalid value"
2, 99, UNKNOWN, FName, "Jack is not a known FName value"
3, 99, UNKNOWN, LName, "LName contains an invalid value"
4, 99, Contact, Phone, "Phone contains an invalid value"
5, 99, Contact, Email, "Post is not a known Email value"
6, 99, UNKNOWN, Phone, "ZZZZ is not a known Phone value"

Now what I'd like to get out of this data is:

ID, Record, Group, Item, ErrorMessage
1, 99, Person, FName, "FName contains an invalid value"
3, 99, UNKNOWN, LName, "LName contains an invalid value"
4, 99, Contact, Phone, "Phone contains an invalid value"
5, 99, Contact, Email, "Post is not a known Email value"

So I only get one error for each item and where the group is defined I want that rather than the line with "UNKNOWN" in the Group

All I know going into the query is the Record id (99)

Also it's on Oracle 10

[roldar]

How about:

(SELECT DISTINCT Item, ID, Record, Group, ErrorMessage FROM table WHERE Group <> "UNKNOWN" AND Record=99)

UNION

(SELECT DISTINCT Item, ID, Record, Group, ErrorMessage FROM table WHERE Group = "UNKNOWN" AND Record=99 AND Item NOT IN(SELECT Item FROM table WHERE Group <> "UNKNOWN" AND Record=99))