FIRST, LAST Aggregate functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FIRST, LAST Aggregate functions

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2006-01-19 : 12:29:11

MSAccess has two nice aggregate functions called FIRST and LAST. If you don't know how they work, FIRST simply returns the first record in the aggregate, LAST returns the last record.

It seems that these functions do not appear in SQL_Server. Is there any way to code SQL so that we get the same functionality?

BTW, is there any reason why MS does not incorporate these functions.

Dom

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-19 : 12:59:18

quote:
Originally posted by DOlivastro

FIRST simply returns the first record in the aggregate, LAST returns the last record.

First and last record according to what?

The Access 95 through Access 2000 help files say: "Since records are normally returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary."

The Access 2002 help says, "These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions."

jhermiz

3564 Posts

Posted - 2006-01-19 : 16:51:24

What about good ol Min() and Max() ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

DOlivastro
Starting Member

41 Posts

Posted - 2006-01-19 : 21:05:42

To Arnold Fibble: Thanks, I didn't know that MS had changed the definition of FIRST and LAST.

To jhermiz: It is not the same a Min and Max. I want a function that returns the first record after the records have been ordered. Also, this is not the same TOP 1, since I don't want just the first record, I want the first record of every group composed by a GROUP BY clause and ordered by a ORDER BY clause.

For example, lets say a group is resturned as follows (GROUP BY Name, ORDERED BY age):

Name Age Score
Dom 13 70
Dom 15 65
Dom 20 90

Then Min (Score) will return 65, but First (Score) will return 70.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-20 : 01:58:21

This may help you

Declare @t table(Name varchar(20),Age int,Score int)
Insert into @t 
	select 'Dom', 13, 70 union all
	select 'Dom', 15, 65 union all
	select 'Dom', 20, 90 union all
	select 'Dim', 33, 12 union all
	select 'Dim', 25, 56 union all
	select 'Dim', 67, 78 
select * from @t T
where score=(select top 1 score from @t where name=T.name)

Madhivanan

Failing to plan is Planning to fail

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-20 : 16:28:18

Select TOP 1 ..... order by ASC would give you just the FIRST one
Select TOP 1 ..... order by DESC would give you the LAST one

notspecified
Starting Member

2 Posts

Posted - 2006-02-02 : 14:44:55

Here is the real problem.

Here is sample data from table fakeTable ordered by Date DESC.

ID Date col1 col2 col3 col4
1 2005-01-03 NULL 27 32 NULL
1 2005-01-02 NULL 29 NULL 44
1 2005-01-01 99 13 5 12

I want to write a query like this.

SELECT ID, FIRST(col1), FIRST(col2), FIRST(col3), FIRST(col4)
FROM fakeTable
GROUP BY ID
ORDER BY Date DESC

This is the result that I want.

ID col1 col2 col3 col4
1 99 27 32 44

Of course I can't do this because 1) there is no aggregate function FIRST, 2) I can't specify Date in the ORDER BY clause because it is not included in the GROUP BY clause and 3) MS SQL aggregate functions do not utilize ORDER when calculating.

I've solved the problem with a stored procedure but, even though I didn't use cursors in my stored procedure, it takes unacceptable long to process.

It would be useful if there was a built in aggregate function for this.

notspecified
Starting Member

2 Posts

Posted - 2006-02-02 : 17:11:24

Here is a possible solution.

SELECT ID,

CAST (
CASE
WHEN LEN(MAX(CONVERT(varchar(50),Date,126) + '|' + CAST(col1 AS varchar(50)))) > 0
THEN RIGHT(
MAX(CONVERT(varchar(50),Date,126) + '|' + CAST(col1 AS varchar(50))),
LEN(MAX(CONVERT(varchar(50),Date,126) + '|' + CAST(col1 AS varchar(50)))) -
CHARINDEX('|',MAX(CONVERT(varchar(50),Date,126) + '|' + CAST(col1 AS varchar(50)))
)
)
ELSE NULL
END
AS Float) AS col1

FROM fakeTable
GROUP BY ID

You can apply the same function to col2, col3 and col4 in the same query and you will get the FIRST non-null value for each col as if ordered by Date DESC.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-03 : 19:04:54

quote:
Originally posted by DOlivastro

For example, lets say a group is resturned as follows (GROUP BY Name, ORDERED BY age):

Name Age Score
Dom 13 70
Dom 15 65
Dom 20 90

Then Min (Score) will return 65, but First (Score) will return 70.

This is where you make your biggest mistake, there is no "first" or "last".

The tables:

Name         Age      Score
Dom          13       70
Dom          15       65
Dom          20       90

Name         Age      Score
Dom          15       65
Dom          13       70
Dom          20       90

Are identical from a relational database perspective.
There is no notion of "first" and "last", you have to order by something, to make sense of your nonsense.

rockmoose

irashkin
Starting Member

1 Post

Posted - 2007-07-09 : 07:53:35

Perhaps FIRST/LAST is a misnomer, but I am among those who would love a function like this on a regular basis. Why? Sometimes I need to create new rows based on old rows - say, a summary row, or something similar. For instance, suppose I have a user table, with name, company, and company address. I want to get a list of companies, and I would like to include an address. Now, there may be multiple addresses, as users have been entered either for different branches, or at different times with teh company address changing.

So for starters I can do
select distinct companyname from thistable
but I don't get the address.

I could do
select companyname, min(address), min(city), [etc.]
group by companyname
but a quick sanity check tells me that I may end up with mismatched address/city/etc. combinations.

See, I don't really care which address I use - I just want something in there that at least MIGHT be valid - but I do want a legit address, not something like 123 Main St., San Francisco, AZ. San Fancisco just is not in Arizna (ok, maybe there is one there by that name, but you get my meaning).

So I don't care if it is FIRST or LAST, but I want a deterministic row selector - each column that I use that aggregate for should come from the same row.

Of course, I can (and have) written my own functions for this, or just built the logic into my procedures as needed, but it really would be handy to have a built in function, and mainly, I just wanted to point out the validity (n my mind at least) of such a request.

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-09 : 11:04:43

The physical order of data in a database has no meaning, and since Access2002 has clarified what it means, they have internally performed a hack to now which row was inserted before another

You need an identity column in the database or a datetime column that defaults to getdate() on insert

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-07-09 : 21:48:06

quote:
Originally posted by irashkin

See, I don't really care which address I use

I call bullshit. Why would any old address be of any more use to anyone than any other? It sounds to me like the address may not as well be there if it doesn't matter what it is. Seems like a dodgy requirement/business case to me.

Just do it properly - do a table join with a min(), max() or some other deterministic function in the predicate. Fix your data model if you have to - it certainaly seems broken if you have the "same" company in a table more than once.

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-17 : 13:34:48

I realize this post is a little dated, but I have a similar problem, and thought someone might be able to offer a solution. first() and last() would be perfect, but if requiring those functions means there's a problem in my data model, I'd like to find out how to fix that too :)

So, for simplicity's sake, let's say I have a products table.

ProductID,
ProductDescription

And a productPrice table that will track pricing history, every product will have a ProductPrice that has IsMSRP = 'True', but custom prices can be set where IsMSRP = 'False'

ProductID,
ProductPrice,
PriceDate,
IsMSRP

If I do a
select * from products inner join productprice
on products.ProductID = productprice.ProductID
order by (isMSRP ASC, PriceDate DESC)

the first records will be custom prices, followed by the most recent MSRP prices. So how can I do a
select products.*, () AS [CustomPrice], () AS [MSRP]
using an inner join, and not using any nested selects
(I know nested selects would be easy, but I wise-man once told me that pretty much any nested select can be done using joins, and I also can't do any indexed views using nested selects which is what I'm looking for)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-17 : 14:31:34

Hi Kevin,
Welcome to SQL Team! Read this article first
http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server
and then this one
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

and then post your question (if you sill have one) as a new topic

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-18 : 10:05:02

Kinda need to know the keys of the tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Rahm
Starting Member

1 Post

Posted - 2008-03-19 : 06:48:14

Hi,
I am new to T-SQL and come from MS Access background.
Like Kevin, I am used to a GROUP BY query with First and Last of one of the fields, picked from the sorted table/recordset below.

Question:
How do I get the latest price for each product in the list of product & prices in the following table.

DDL:
CREATE TABLE [dbo].[myTABLE](
[PRODUCT] [nchar](10),
[PRICE] [decimal](18, 4),
[DATE_PRICED] [datetime]
)

DATA USED in TABLE
PRODUCT PRICE DATE_PRICED
RT10-111/VY, 1, 2007-03-16
RT10-112/VY, 0.95, 2007-06-01
RT10-112/VY, 0.95, 2006-05-16
RT10-112/VY, 0.92, 2002-09-23
RT10-115, 3.2, 2007-04-03
RT10-116, 2.5, 2007-12-07
RT10-120/VY, 1.2, 2006-03-17
RT10-120/VY, 1.15, 2004-04-12
RT10-201/VY, 4.06, 2004-11-29
RT10-211, 0.59, 2007-09-12
RT10-212/BD, 0.85, 2007-01-10
RT10-213, 31.5, 2007-11-01
RT10-327/MA, 1.78, 2001-06-29
RT10-328, 1.26, 2007-09-27
RT10-420/FL, 0.3991, 2007-11-05
RT10-421/UN, 0, 2006-04-04
RT10-421/UN, 60, 2004-09-13
RT10-422/PE, 0.45, 2006-07-21
RT10-422/PE, 0.504, 2003-03-03
RT10-427/FL, 0.3991, 2008-01-04
RT10-427/FL, 0.4016, 2007-12-17
RT10-430/FL, 0.4339, 2004-06-14
RT10-430/FL, 0.42, 2004-04-05
RT10-435/FL, 0.49, 2004-05-03

THE QUERY
I would use the following MS Access query but cannot find the equivalent T-SQL quer/ies to do this:

SELECT PRODUCT, First(DATE_PRICED) AS LASTDATE, First(PRICE) AS LASTPRICE
FROM MYTABLE
GROUP BY PRODUCT
Please note I have already run a query to sort the above table in descending date for each product.

RESULTSET:
FOR EACH SINGLE PRODUCT, THE RESULTSET WOULD THEN HAVE BEEN:
CODE LASTPRICE LASTDATE
RT10-111/VY 1 2007-03-16
RT10-112/VY 0.95 2007-06-01
RT10-115 3.2 2007-04-03
RT10-116 2.5 2007-12-07
RT10-120/VY 1.2 2006-03-17
RT10-201/VY 4.06 2004-11-29
RT10-211 0.59 2007-09-12
RT10-212/BD 0.85 2007-01-10
RT10-213 31.5 2007-11-01
RT10-327/MA 1.78 2001-06-29
RT10-328 1.26 2007-09-27
RT10-420/FL 0.3991 2007-11-05
RT10-421/UN 0 2006-04-04
RT10-422/PE 0.45 2006-07-21
RT10-427/FL 0.3991 2008-01-04
RT10-430/FL 0.4339 2004-06-14
RT10-435/FL 0.49 2004-05-03

Is there anyone who has carried out similar queries with MS Access and migrated them to T-SQL?
Alternatively, anyone who does similar query in T-SQL itself?

Many thanks for your assistance and patience.

Rahm M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 07:20:45

SELECT t1.PRODUCT, t1.DATE_PRICED AS LASTDATE, t1.PRICE AS LASTPRICE
FROM MYTABLE t1
INNER JOIN (SELECT PRODUCT, MAX(DATE_PRICED) AS LATESTDATE
FROM MYTABLE
GROUP BY PRODUCT)t2
ON t1.PRODUCT=t2.PRODUCT
AND t1.DATE_PRICED=t2.LATESTDATE

CZahrobsky
Starting Member

1 Post

Posted - 2011-05-05 : 12:50:11

It's not pretty, but I combine fields to sort alpha with my target field in the last position, get the MAX or MIN of that, and return the substring of just the target field:

SELECT [PRODUCT], CONVERT(decimal, 
         SUBSTRING(
            MIN(convert(varchar, [DATE_PRICED], 126) + ' '  
              + convert(varchar, [PRICE])
         ), 25, 50) AS FIRSTPRICE
GROUP BY [PRODUCT]

Notes: The MIN(convert(varchar, [DATE_PRICED], 126) allows the date part to sort alphabetically in this format 2011-05-05T11:45:55.750, hence the SUBSTRING 25 characters in. The 50 is arbitrary, as long as it encompasses your desired field length.

The tree of knowledge has many tangled roots.

sunnysood
Starting Member

2 Posts

Posted - 2012-12-17 : 07:11:18

quote:
Originally posted by DOlivastro

MSAccess has two nice aggregate functions called FIRST and LAST. If you don't know how they work, FIRST simply returns the first record in the aggregate, LAST returns the last record.

It seems that these functions do not appear in SQL_Server. Is there any way to code SQL so that we get the same functionality?

BTW, is there any reason why MS does not incorporate these functions.

Dom

LOL

sunnysood
Starting Member

2 Posts

Posted - 2012-12-17 : 07:16:32

quote:
Originally posted by DOlivastro

MSAccess has two nice aggregate functions called FIRST and LAST. If you don't know how they work, FIRST simply returns the first record in the aggregate, LAST returns the last record.

It seems that these functions do not appear in SQL_Server. Is there any way to code SQL so that we get the same functionality?

BTW, is there any reason why MS does not incorporate these functions.

Dom

I had the same problem today. the first and last functions do not appear to work in sql server but there are ways around it using the top clause.

Get the first entry:
select top 1 column_name
as first_entry
from table_name

Get the last entry:
select top 1 Order_Price
as Last_Entry
from table_name
order by column_name desc

cheers

LOL

KipB7
Starting Member

1 Post

Posted - 2013-04-17 : 19:15:21

For Microsoft SQL Server, CROSS APPLY can save the day in cases like this. Here's an example where you want to know the scores on the contest by Age, with high/low/average/count for each age group.

IF OBJECT_ID('T1','U') IS NULL --drop table T1
	CREATE TABLE T1 (Name varchar(20), Age int, Score DECIMAL(9,0))
IF NOT EXISTS(SELECT * FROM T1)
	Insert into T1---Age-Score--
		select 'Dam', 13, 70 UNION ALL
		select 'Dem', 13, 65 UNION ALL
		select 'Dim', 25, 12 UNION ALL
		select 'Dom', 25, 56 UNION ALL
		select 'Dum', 67, 78 UNION ALL
		SELECT 'Duz', 25, 57


SELECT Age, HI.NAME[HiGuy],HI.Score[HiScore],LO.NAME[LoGuy],LO.Score[LoScore], AvgScore, HowMany[#]
FROM (SELECT DISTINCT Age FROM T1) t0
CROSS APPLY (SELECT TOP 1 Name, Score FROM T1 WHERE T1.Age=t0.Age ORDER BY Score DESC) HI
CROSS APPLY (SELECT TOP 1 Name, Score FROM T1 WHERE T1.Age=t0.Age ORDER BY Score ASC) LO
CROSS APPLY (
  SELECT COUNT(*)[HowMany], CONVERT(DECIMAL(9,2),AVG(score))[AvgScore] FROM T1 WHERE T1.Age=t0.Age
  ) AVGSC
ORDER BY Age


/*
Age HiGuy HiSc LoGuy LoSc AvgSc  #
13  Dam    70  Dem    65  67.50  2
25  Duz    57  Dim    12  41.67  3
67  Dum    78  Dum    78  78.00  1
*/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources