How to get rows with Maximum id with condition of a table in SQL Server
i have a table similar this
id-value-RowInid 1-xy-1 1-xx-2 1-xz-3 2-xx-1 2-xr-2 3-xq-1 4-xa-1 4-xc-2 ...
i need a function for this table with similar output to get maximum of RowInid in separated id group
1-xz-3 2-xr-2 3-xq-1 4-xc-2 ...
no need for group by or max at all
select id, value, rowinid from ( select *, row_number() over (partition by id, order by rowinid desc) rn from yourtable ) v where rn = 1
You just need to use MAX(RowInid) with GROUP BY Id, value
SELECT ID, VALUE, MAX(RowInid) FROM myTable GROUP BY ID, VALUE
EDIT: As you updated your question, you can get value field using sub-query like this:
SELECT ID, VALUE, RowInid FROM myTable t1 WHERE RowInid = ( SELECT MAX(RowInid) FROM myTable WHERE id = t1.id GROUP BY id ) ORDER BY id ASC;
You can also achieve this using INNER JOIN like this:
SELECT t2.ID, VALUE, t2.RowInid FROM myTable t1 INNER JOIN ( SELECT ID, MAX(RowInid) AS RowIniD FROM myTable GROUP BY ID ) AS t2 ON t1.ID = t2.ID AND t1.RowInid = t2.RowInid ORDER BY t1.ID ASC;