Solved

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

Posted on 2010-09-02
15
820 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 166 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 167 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 167 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

689 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