SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 FIRST, LAST Aggregate functions
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/18/2013 :  03:30:06  Show Profile  Reply with Quote
quote:
Originally posted by sunnysood

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


FIRST_VALUE() and LAST_VALUE() are corresponding functions in sql server and is available only from sql 2012

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/18/2013 :  03:31:30  Show Profile  Reply with Quote
quote:
Originally posted by KipB7

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
*/



yep...i've blogged about it too

see scenario 2

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

only issue is for really large datasets the performance of subquery may become a concern

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000