Aggregate functions - First, Last, Max, Min etc

Question

Post reply

Aggregate functions - First, Last, Max, Min etc

Deorca

SSC-Addicted

Points: 450
More actions
May 29, 2003 at 7:28 am

#80166

I'm upgrading from Access to SQL Server and have to translate to T-SQL a number of queries which use the 'First' and 'Last' functions that Jet uses ... only I can't seem to find equivalents in T-SQL. Is there an easy answer to this? please, please someone tell me there's an easy answer ..

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Look in BooksOnline for Aggregate functions:

MIN(), MAX(), AVG(), SUM(), COUNT()...

jmadren SSCrazy Points: 2055 More actions · Answer 2

But he's asking about First and Last, which don't have corresponding functions in T-SQL. I've wished for these at times myself.

Jay Madren

jpipes SSChampion Points: 12230 More actions · Answer 3

What's the difference between MIN and First and MAX and Last. YOu can also use TOP clause and the order by clause to produce the results needed.

Edited by - jpipes on 05/29/2003 08:48:18 AM

jmadren SSCrazy Points: 2055 More actions · Answer 4

In Access, the First and Last functions allow you to retrieve the first or last value (field) within a Group, where the order is not by that field (i.e., ordered by some other field).

Jay Madren

jpipes SSChampion Points: 12230 More actions · Answer 5

quote:
In Access, the First and Last functions allow you to retrieve the first or last value (field) within a Group...

That is percisely what MIN and MAX do.

Example:



SELECT OrderGroup, MIN(ReceivedOn) AS EarliestReceived, MAX(ReceivedOn) AS LatestReceived

FROM OrderTable

GROUP BY OrderGroup

Deorca SSC-Addicted Points: 450 More actions · Answer 6

There are Min & Max functions for Jet/Access, I assume they're equivalent to their namesakes in T-SQL. This is doing my head in - can anyone out there think of a workaround?

jpipes SSChampion Points: 12230 More actions · Answer 7

quote:
..can anyone out there think of a workaround?...

What are you trying to work around? If you post the Jet code, we can translate it to T-SQL...

Deorca SSC-Addicted Points: 450 More actions · Answer 8

quote:
What are you trying to work around? If you post the Jet code, we can translate it to T-SQL...

It's alright, I've tried using Min & Max and it all appears to work fine. Thanks fellas.

Aggregate functions - First, Last, Max, Min etc

Cookies on SQLServerCentral