Tricky query

[smn198]

I've got a little stuck! I as the solution I have doesn't work properly and it is sloooooooooow!

I have a table setup with the following data:

[green]ID  Type    Value   Date[/green]
1   1       Yes
1   1       No
1   2       No      01/01/2004
1   2       Yes     01/01/2005
1   2       Yes     01/01/2005
2   1       Unknown

I need a query that will produce the same table but leave out the best row from each group of ID, Type and Value.

The best is defined as the one with the greatest date. Where 2 or more in the same group have the greatest date or none all have null dates, 'No' is the best followed by 'Yes' and finally 'Unknown'. Only one from each group should be excluded so if there are two identical rows deemed the best, one should reamin and the other be excluded. (I have been trying to use subqueries, group by and top to do this but have not figured it out yet.)

The result I would require is as follows (red lines to be removed):

[green]ID  Type    Value   Date[/green]
[red]1   1       Yes[/red]
1   1       No
[red]1   2       No      01/01/2004[/red]
[red]1   2       Yes     01/01/2005[/red]
1   2       Yes     01/01/2005
[red]2   1       Unknown[/red]

If this could be done as a straight query that would be preferable but if there is a major performance gain by using another method I would be interested in that.

Thank you very much in advance!

Steve

 

[Glenn9999]

OK so what rows do you want returned? What it looks like to me is:

1) If Value = Yes then we want the greatest value in date.
2) If Value = No then we want values without dates?
3) Is there a situation where we would return Value = Unknown?

"A well defined problem is a problem half-answered.

 

[smn198]

Hi Glenn

Thanks for your reply

I want all the columns in the original table returned and just a few of the rows.

It may be easier if I just ask for the ones I want to loose. Then I can subtract them from the original query.

In that case I want to select 1 row for each variation of ID, Type and Value.

The selected row will have the greatest date in that group. If more than one record has the greatest date, a no should be selected before a yes and a yes before an unknown.

Hope that makes more sense!

Steve

 

[PHV]

What DBMS ?
What have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244