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:
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):
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
I have a table setup with the following data:
Code:
[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
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):
Code:
[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