[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

linq to sql query how to select highest and lowest ID from table

Posted on 2010-09-02
15
Medium Priority
?
829 Views
Last Modified: 2013-11-11
Hi

I need two linq queries that brings back the highest and lowest records from a table

For example this is the table

ID    Text

1     dog
2     cat
3     fish
4     bird
8    cow


I need a query to bring back the highest ID (8)  also a query to bring back the lowest id (1)
0
Comment
Question by:ToString1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33586819
you could use the aggregate functions MAX and MIN

eg

select MAX(ID) from mytable
0
 

Author Comment

by:ToString1
ID: 33586831
Ohhh forgot say I can have table called FOO

ID    Text

1     dog
2     cat
3     fish
4     bird
8    cow


Then I want to bring back a FOO class with the lowest ID

and a FOO class with the highest ID

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33586838
linq to sql I think then is

(from t in MyTable select (t.Id)).Max()  

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:ToString1
ID: 33586842
Sorry also forgot to say that I need a LINQ to SQL sample
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 664 total points
ID: 33586873
Anything like this?

(from f in FOO select (f.Id)).Max()  

(from f in FOO select (f.Id)).Min()  
0
 
LVL 4

Expert Comment

by:ricovox
ID: 33586906

//get the min and max
var query = from rec in <TABLE>
            select rec.ID;

var min = rec.Min();
var max = rec.Max();

var minID = (from rec in <TABLE>
            where rec.Text == min
            select rec.Id).First();

var maxID = (from rec in <TABLE>
            where rec.ID == max)
            select rec.Id).First();

Open in new window

0
 
LVL 4

Expert Comment

by:ricovox
ID: 33586942
Oops. Sorry about my previous post.
I did not see that you changed the table format.
0
 
LVL 4

Expert Comment

by:ricovox
ID: 33586967

//get the min and max
var query = from rec in <TABLE>
            select rec.ID;

var min = rec.Min();
var max = rec.Max();

var minID = (from rec in <TABLE>
            where rec.ID == min
            select rec).First();

var maxID = (from rec in <TABLE>
            where rec.ID == max)
            select rec).First();

Open in new window

0
 
LVL 4

Expert Comment

by:ricovox
ID: 33586990
actually minID and maxID will be the min and max records, respectively (not just the ID)

The problem with rockiroad's approach is that it will return ONLY the min and max ID (not the full record or even the Text)
0
 
LVL 4

Expert Comment

by:ricovox
ID: 33587006
Also, make sure to use the exact cases as you have them in the database.

E.g. use select rec.ID or rec.Id depending on whether or not it is ID or Id in the database.
0
 
LVL 4

Accepted Solution

by:
ricovox earned 668 total points
ID: 33587036
if you just care about the text, you can use this code.

It will give you textMin=dog and textMax=cow, from your example.

//get the min and max ID as listed above

var textMin = (from rec in <TABLE>
            where rec.ID == min
            select rec.Text).First();

var textMax = (from rec in <TABLE>
            where rec.ID == max)
            select rec.Text).First();

Open in new window

0
 
LVL 21

Expert Comment

by:naspinski
ID: 33587041
Assuming your ID field is an auto-incremented int (if not, you just need to do a .OrderBy(x => x.ID) before the .Select):
int first = dataContext.tableName.First().Select(x => x.ID);
int last = dataContext.tableName.Last().Select(x => x.ID);

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33587053
>> The problem with rockiroad's approach is that it will return ONLY the min and max ID (not the full record or even the Text)

Yea well I guess reading part of the question which says  "I need a query to bring back the highest ID (8)  also a query to bring back the lowest id (1)" that's why I posted just that. But your welcome to take the credit for a more fuller answer.
0
 
LVL 21

Assisted Solution

by:naspinski
naspinski earned 668 total points
ID: 33587069
sorry, didn't read your lower posts, to return foo objects, its even easier - linq is so easy! :)
FOO first = dataContext.Foos.First();
FOO last = dataContext.FOOs.Last();

Open in new window

0
 

Author Comment

by:ToString1
ID: 33587117
ok thanks for your answers; sorry if I did not make myself clear

So I want to return say the first FOO object but I also need to combine it with a another clause.   Here I am checking for a foo with a certain ID but I also want the FIRST and the second query would be the NEXT foo object with the next ID

var firstFoo = db.foo.Where(q => q.ID == CurrentFoo.ID);
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question