Simple data, Complex query on SQL Server

Question

I need to make a query over an SQL Server table but I don't know exactly how.

Consider this table (the real table is much more complex, Ord1 and Ord2 are dates that could be null, but i simplified it to this case):

Data of MyTable

ID  MaqID   Ord1    Ord2 
------------------------
1   144     4       3 
2   144     2       1 
3   12      2       3 
4   144     3       5 
5   12      3       1
6   144     4       2 
7   12      2       4 
8   144     2       3 
9   12      1       5 
10  12      3       2

I need records for specific MaqID in Specific Order. I get that with this Query:

SELECT * FROM myTable WHERE MaqID=144 ORDER BY MaqID, Order1 DESC, Order2

Wich give me:

ID  MaqID   Ord1    Ord2
------------------------
6   144     4       2
1   144     4       3
4   144     3       5
2   144     2       1
8   144     2       3

Now, I need a single query that, for each MaqID, return the first ID for each subquery following above order. The result should be:

Expected result

MaqID    ID
-----------
144      6
12       5

I have already try distinct conbination of TOP a MAX, but TOP result only one result and i need one for each MaqID, and for Max I have not field to maximize.

To sumarize: I need the first ID for each MaqID from a subquery in a specific order

Any ideas? Thanks!

Answer 1

You can do this using row_number():

select t.*
from (select t.*,
             row_number() over (partition by macid Order1 DESC, Order2) as seqnum
      from mytable t
     ) t
where seqnum = 1;