• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

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

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
ToString1
Asked:
ToString1
  • 6
  • 4
  • 3
  • +1
3 Solutions
 
rockiroadsCommented:
you could use the aggregate functions MAX and MIN

eg

select MAX(ID) from mytable
0
 
ToString1Author Commented:
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
 
rockiroadsCommented:
linq to sql I think then is

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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ToString1Author Commented:
Sorry also forgot to say that I need a LINQ to SQL sample
0
 
rockiroadsCommented:
Anything like this?

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

(from f in FOO select (f.Id)).Min()  
0
 
ricovoxCommented:

//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
 
ricovoxCommented:
Oops. Sorry about my previous post.
I did not see that you changed the table format.
0
 
ricovoxCommented:

//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
 
ricovoxCommented:
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
 
ricovoxCommented:
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
 
ricovoxCommented:
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
 
naspinskiCommented:
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
 
rockiroadsCommented:
>> 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
 
naspinskiCommented:
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
 
ToString1Author Commented:
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now