Solved

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

Posted on 2010-09-02
15
815 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

770 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