Looking for a "finesse" query to eliminate additional rows 1

[ookete]

I have a very large table that is basically structured like this:

Category  Score  Type
1         90     0
2         3      0
2         87     1
3         65     0

Notice that the Category 2 has two records (of type 0 and 1). What I need to do is select a recordset that excludes all 0-type records where its category also has a 1-type. So the result would be:

Category  Score  Type
1         90     0
2         87     1
3         65     0

Note that the Score of the 1-type might not always be larger than the equivalent 0-type. I thought of just grouping on Category using the MAX aggregate function, but that won't always work for score. I need the actual score assigned to the MAX Type (the type of 1). Does that make sense?

I know of ways I could do this with a few joins, but I wanted to see if any of you had a more "finesse" way of achieving this than I was thinking about.

Thanks a lot!

 

[gmmastros]

This is how I would do it.

Select Table.Category,
       Table.Type,
       Table.Score
From   Table
       Inner Join
         (
         Select Category,
                Max(Type) As Type
         From   Table
         ) As A
         On  Table.Category = A.Category
         And Table.Type = A.Type

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ookete]

George, thanks for the reply. I figured I'd have to join back to the table, but your solution sparked a simple way for me to do it.