Solved

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

Posted on 2010-09-02
15
813 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
Comment Utility
you could use the aggregate functions MAX and MIN

eg

select MAX(ID) from mytable
0
 

Author Comment

by:ToString1
Comment Utility
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
Comment Utility
linq to sql I think then is

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

0
 

Author Comment

by:ToString1
Comment Utility
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
Comment Utility
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
Comment Utility

//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
Comment Utility
Oops. Sorry about my previous post.
I did not see that you changed the table format.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:ricovox
Comment Utility

//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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now