sql - Remove dupes from recordset excluding columns from dupe condition -

I am against an mssql database like a SQL query ...

  ID, type, start, stop, select all from a union, select ID, type, start, stop, one, two, three, four U union Select all ID, type, start, stop,  Results in 

:

  line | Starting an ID type One two three four---- + ------------------------------------- ------------------------- 1. 1 a 2010-01-01 2010-01-31 100 1000 1000 100 2 | 1 a 2010-02-01 2010-12-31 100 500 500 50 3 | 1B 2010-01-01 2010-01-31 100 NULL NULL 100 4 | 1B 2010-01-01 2010-12-31 100 Nal faucets 100 5 | 1C 2010-01-01 2010-01-31 0 Tube Touches 100 6 | 1c 2010-01-01 2010-12-31 0 NULL NULL 100  

However, I would prefer the following results instead ...

  line | Starting an ID type One two three four---- + ------------------------------------- ------------------------- 1. 1 a 2010-01-01 2010-01-31 100 1000 1000 100 2 | 1 A 2010-02-01 2010-12-31 100 500 500 50 4 1B 2010-01-01 2010-12-31 100 Nal Faucets 100 6 | 1 C 2010-01-01 2010-12-31 0 NULL NULL 100  

That is, after destroying the lines 3 and 5, they are in 4 and 6 rows in every way Unfortunately, the unfortunate line of low value is to be removed from but to stop -column, and inhibit -column in every way.

How can I do this? I was thinking something like that ...

  from SELECT * (SELECT id, type, start, stop, one, two, three, four from a UNION ALL select id, type, start, Stop all, choose one from the University, type, start, stop, one, two, three, four to C order type ASC) by Ace Type Group ... HOWING ???  

I need guidance, please help.

(And no, I am not in a position to change any position, I have to work with the given situation.)

Similar questions are asked and answered. For example:

And your situation is also easy (if I understood the correct description of your problem):

  ID, type, start, max (stop), one , <,>  (two) three, four to (...) type group id, type, start, one, two, three, four instead of  

. . ...) You insert your selection with A, B and C. Instead of bus (id, type, start) -> (one, two, three, four) you have to exit (type ID, type) order .

(start, stop) -> (one, two, three, four) (which means that you should choose the other columns according to the maximum (stop)), this query is generally sensible The execution plan is:

  Choose id, type, start, stop, one, two, three, four from (...) type where stop = (max (stop). . T2 where t2.id = types.id and T2.type = types.type and t2.start = types.start)  

but it depends on the fact that the data How to Distribute in Your Source Tables Is done and which indexes are present. In some cases the solutions of the links above may still be better.