CODEWITHSUNDEEP
sqllinqsql-server-2008entity-frameworksql-server-2008-r2
KentZhou
KentZhou

Reputation: 25553

How to write SQL and LINQ to get the one record from a result with where condition?

Suppose I have a table Tab1 with millions of records:

Tab1(id, id1,id2, value, ...)

With SQL like: 

Select * from Tab1 Where id1=123;

I get 100 records. then I want to get one which has maximum of id value from this 100 records.

How to write the simplest SQL for this?

When I use EF to match this table to a Entity in EF, how to write the LINQ to get same result?

Upvotes: 0

Views: 87

Answers (2)

Sethcran
Sethcran

Reputation: 788

This can be done with ORDER BY and TOP.

In SQL:

SELECT TOP 1 *
FROM Tab1
WHERE id1 = 123
ORDER BY id desc

In Linq with EF:

Tab1.Where(t => t.id1 == 123).OrderByDescending(t => t.id).FirstOrDefault();

Upvotes: 4

Tobia Zambon
Tobia Zambon

Reputation: 7619

I think the simplest way can be this:

Select * from Tab1 Where id1=123 AND id = (SELECT First(Max(id)) from Tab1 where id1 = 123)

You can remove the First if values are distinct (I don't know how Max behaves if there are multiple rows with the same maximum value)

About Linq you can use something similar to:

Tab1.Where(t => t.id == 123).OrderByDescending(t => t.id).FirstOrDefault();

Upvotes: 0

Related Questions