?
Solved

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

Posted on 2010-09-02
15
Medium Priority
?
822 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

743 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