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
...

Answers


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;

See this SQLFiddle

See more about GROUP BY and MAX in SQL Server.


Need Your Help

CSV Columns to Arrays in Python

python arrays csv

I'm having a hard time dealing with what seems to me to be a simple problem. I'm trying to import a csv and split its columns into arrays that I can run different operations on, then zip() back tog...

Python sending email too slow

python performance email smtp

I have the script below which I'm using to send say 10 messages myself<->myself. However, I've noticed that Python really takes a while to do that. Last year I needed a system to send about 200 ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.