CODEWITHSUNDEEP
sqlentity-framework
Sina Hajian
Sina Hajian

Reputation: 17

select rows from table where id=max

I have table that has several rows with the same ProcessID but different ID. How can I select rows with max ID and different ProcessID in a SQL query and Entity Framework?

My table:

ID     | ProcessID  | MESSAGE    | STATUS
-------+------------+------------+--------
100    | 100        | test       | 2
101    | 100        | test       | 2
102    | 100        | test       | 3
103    | 100        | test       | 4
104    | 104        | test       | 5
105    | 104        | test       | 6
106    | 104        | test       | 7
107    | 104        | test       | 8
108    | 104        | test       | 09

When search:

ID     | ProcessID  | MESSAGE    | STATUS
-------+------------+------------+---------    
103    | 100        | test       | 4
108    | 104        | test       | 09

Upvotes: 0

Views: 501

Answers (4)

Doug F
Doug F

Reputation: 167

How about a simple group by with max(id)

select max(id), processID, message, status   
from TableFoo  
group by processID

It should carry the message,status with the max(id)

Upvotes: 0

Slava Utesinov
Slava Utesinov

Reputation: 13488

var data = (from item in ctx.Processes
            group item by item.ProcessID into sub
            let maxId = sub.Max(x => x.ID)
            select new
            {
                ID = maxId,
                ProcessID = sub.Key,
                sub.Where(x => x.ID == maxId).First().MESSAGE,
                sub.Where(x => x.ID == maxId).First().STATUS
            }).ToList();

//or this variant
data = (from item in ctx.Processes
        join gr in (from temp in ctx.Processes
                    group temp by temp.ProcessID into sub
                    select sub.Max(x => x.ID))
        on item.ID equals gr
        select new
        {
            item.ID,
            item.ProcessID,
            item.MESSAGE,
            item.STATUS,
        }).ToList();

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Entity framework suggests you are using SQL Server. Then you can use my favorite method, which doesn't use a subquery:

select top (1) with ties t.*
from t
order by row_number() over (partition by processid order by id desc);

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You could use ROW_NUMBER:

SELECT  ID, ProccessID, MESSAGE, STATUS 
FROM (SELECT *, 
         ROW_NUMBER() OVER(PARTITION BY ProcessID ORDER BY id DESC) AS rn
      FROM tab) sub
WHERE rn = 1;

Upvotes: 1

Related Questions