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)
 What is equivalent for Last in SQLserver

Author  Topic 

azeemuddinn
Starting Member

1 Post

Posted - 2005-06-08 : 02:09:53
What is equivalent for Last function in Acccess to that for
SQLserver

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-08 : 02:20:23
If your table has Datetime field, then

Select top 1 col from yourtable Order by DateTimeCol Desc

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 02:21:03
Last ?? Last Record ??..

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-08 : 03:39:01
the order of the records in the table is not really important unless you have some field which may be used as criteria such as identity column, incrementing primary key, date field, timestamp, etc.

you can however access the "last record" in a recordset by using top 1 coupled with order by desc or asc, depending on how you want the records to be returned

--------------------
keeping it simple...
Go to Top of Page

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-08 : 04:40:07
How exactly does last function in Access behave ... ???
The answer to this question can only be given after knowing what exactly is
ur req.



Thanks,
Vivek
Go to Top of Page

stultuske
Starting Member

3 Posts

Posted - 2006-03-10 : 08:15:12
the function last(..) in ms acces gives the last element of the column you're looking in

example: tblOne has one column colOne, which contains:

one
two
three

the statement: select last(colOne) as last from tblOne
gives: three

transferring this function to ms sql server is doable, but only if there is (at least) one sorted column in the table.

to get the last element of the column, you would have to do:

select top(1) colOne from tblOne Ordered by colOne DESC

this would give the wrong result, namely "two" (because the column isn't sorted)

so, to find in this case the right answer, we would need another column,for example an incrementing ID

colOne colTwo
one 1
two 2
three 3

now we can say:

select top(1)colOne from tblOne ordered by colTwo DESC

since colTwo is sorted, we now get the result "three"

It wouldn't be called research if we knew what we're doing, now would it?
Go to Top of Page

nonono
Starting Member

8 Posts

Posted - 2006-10-03 : 14:23:08
I am using SQL server 2000 witch doesn’t support the First and Last aggregate functions

I'm developing some software witch is badly in need for this function so I decided to give “SQL Server 2005 Express Edition SP1” a try. Sadly with the same result.

example:

select
Convert(Varchar(10), DateTimeStamp, 120) as DailyDate,
Max(Price) as PriceHigh,
Min(Price) as PriceLow,
First(Price) as PriceOpen,
Last(Price) as PriceClose,
Sum(Volume) as TotVolume
from StockQuotes
group by Convert(Varchar(10), DateTimeStamp, 120)


This is not working, the SQL 2K err msg:

Server: Msg 195, Level 15, State 10, Line 5
'First' is not a recognized function name.


according to this site it should work:
http://www.w3schools.com/sql/sql_functions.asp

Anybody know how to retrieve a column from the first (and last) record of the aggregate (group by) set? Is there a way to make it work?

Mvg
Kees
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-03 : 14:46:41
The article tells you :
quote:
FIRST(column) Returns the value of the first record in a specified field (not supported in SQLServer2K)


The FIRST function in 2k5 is used in Reporting Services.

select 
Convert(Varchar(10), DateTimeStamp, 120) as DailyDate,
Max(Price) as PriceHigh,
Min(Price) as PriceLow,
First(Price) as PriceOpen,
Last(Price) as PriceClose,
Sum(Volume) as TotVolume
from StockQuotes
group by Convert(Varchar(10), DateTimeStamp, 120)

To get the OPENING and CLOSING price, you will need a datetime column to see which record is "First" and "Last".

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-03 : 15:07:29
Didn't we just do this?

To really see what happens, have Access connect to SQL Server, start Profile, then run an access query with first, last MIN and MAX and you'll see what it does



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



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-03 : 15:14:04
When I execute this query

SELECT dbo_Orders.CustomerID
, First(dbo_Orders.OrderDate) AS FirstOfOrderDate
, Last(dbo_Orders.OrderDate) AS LastOfOrderDate
, Min(dbo_Orders.OrderDate) AS MinOfOrderDate
, Max(dbo_Orders.OrderDate) AS MaxOfOrderDate
FROM dbo_Orders
GROUP BY dbo_Orders.CustomerID;

SQL Server executes this:

SELECT "CustomerID" ,"OrderDate" FROM "dbo"."Orders"

So what does that tell you? Also, as an aside, all MIN's = FIRST's and MAX's = LAST's....could be a ccoincidence. What doe sthe Access Manual say?

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



Go to Top of Page

nonono
Starting Member

8 Posts

Posted - 2006-10-03 : 15:26:23
quote:
Originally posted by DonAtWork

The article tells you :
quote:
FIRST(column) Returns the value of the first record in a specified field (not supported in SQLServer2K)


The FIRST function in 2k5 is used in Reporting Services.



Thanks, Ok, so I can’t use it like SUM or MAX

quote:
Originally posted by DonAtWork

To get the OPENING and CLOSING price, you will need a datetime column to see which record is "First" and "Last".


That’s no problem I have a datetime column, but if I can’t use a FIRST and LAST function I probably have to use a sub select to make it work. Or in the worst case scenario a second Loop. This wil sadly cost more cpu to compute. For info there is my table definition:
create table StockQuotes
(
DateTimeStamp datetime not null,
Price dec(9,4) not null,
Volume int not null
)

I have to study some more on this problem (any help is appreciated) but fist I’m going to eat something...
Go to Top of Page

nonono
Starting Member

8 Posts

Posted - 2006-10-03 : 16:51:05
quote:
Originally posted by X002548

when I execute this query
SQL Server executes this:

SELECT "CustomerID" ,"OrderDate" FROM "dbo"."Orders"

So what does that tell you? Also, as an aside, all MIN's = FIRST's and MAX's = LAST's....could be a ccoincidence. What doe sthe Access Manual say?

Brett



Hi Brett,

I’m not using – and have no knowledge on msAccess. But what I understand from your reply is that access is fetching all rows and computing de result itself. This is NOT what I want (too much network I/O). But you made it clearer that SQL is not supporting the FIRST and LAST functions. Tanks!

mvg
Kees
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 21:01:29
You need to define First and Last

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-10-04 : 02:16:01
"But you made it clearer that SQL is not supporting the FIRST and LAST functions"

That's not entirely true!

Yes there is no FIRST keyword. But SQL Server does have a "MIN" keyword.

I have no idea what FIRST gives you in Access - but I presume it is the value for the record with MINimum primary key. In which case FIRST (in SQL Server) would be:

(SELECT TOP 1 MyColumn FROM MyTable ORDER BY MyPrimaryKeyColumn1, MyPrimaryKeyColumn2)

Similarly for LAST

Just to reiterate what others have said: FIRST() makes no sense unless you also describe by-what-order the record considered to be "First" is defined.

Kristen
Go to Top of Page

nonono
Starting Member

8 Posts

Posted - 2006-10-04 : 05:05:36
quote:
Originally posted by Kristen

"
Just to reiterate what others have said: FIRST() makes no sense unless you also describe by-what-order the record considered to be "First" is defined.

Kristen



Ok here is some sample data:
create table StockQuotes
(
DateTimeStamp datetime not null,
Price dec(9,4) not null,
Volume int not null
)
create index IND_StockQuotes on StockQuotes (DateTimeStamp, Price, Volume)

/* inserting: some tick by tick data */
insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:00:000',121), 125.0, 50) --market opening--
insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:00:000',121), 125.0, 150)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:00:123',121), 125.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:01:456',121), 126.0, 75)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:04:25:789',121), 124.0, 600)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 09:05:00:000',121), 123.0, 300)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:06:00:000',121), 122.0, 300)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:07:00:000',121), 125.0, 200)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:08:00:000',121), 127.0, 400)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:09:00:000',121), 126.0, 200)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 09:10:00:000',121), 126.0, 100)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:11:00:000',121), 126.0, 100)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:12:00:000',121), 127.0, 200)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:13:00:000',121), 125.0, 400)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:14:00:000',121), 124.0, 100)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 09:15:00:000',121), 124.0, 100)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:16:00:000',121), 123.0, 100)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:17:00:000',121), 124.0, 200)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:18:00:000',121), 125.0, 400)
insert into StockQuotes values (convert(datetime,'2006-10-01 09:19:00:000',121), 126.0, 100)
-- Hourly
insert into StockQuotes values (convert(datetime,'2006-10-01 10:00:00:000',121), 125.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 10:15:00:000',121), 121.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 10:30:00:000',121), 124.0, 125)
insert into StockQuotes values (convert(datetime,'2006-10-01 10:45:00:000',121), 126.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 10:59:59:999',121), 125.0, 325)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 11:00:00:000',121), 125.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 11:15:00:000',121), 124.0, 300)
insert into StockQuotes values (convert(datetime,'2006-10-01 11:30:00:000',121), 123.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 11:45:00:000',121), 122.0, 425)
insert into StockQuotes values (convert(datetime,'2006-10-01 11:59:59:999',121), 121.0, 325)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 12:00:00:000',121), 120.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 12:15:00:000',121), 119.0, 625)
insert into StockQuotes values (convert(datetime,'2006-10-01 12:30:00:000',121), 118.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 12:45:00:000',121), 117.0, 300)
insert into StockQuotes values (convert(datetime,'2006-10-01 12:59:59:999',121), 116.0, 325)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 13:00:00:000',121), 115.0, 325)
insert into StockQuotes values (convert(datetime,'2006-10-01 13:15:00:000',121), 114.0, 225)
insert into StockQuotes values (convert(datetime,'2006-10-01 13:30:00:000',121), 113.0, 300)
insert into StockQuotes values (convert(datetime,'2006-10-01 13:45:00:000',121), 112.0, 825)
insert into StockQuotes values (convert(datetime,'2006-10-01 13:59:59:999',121), 111.0, 325)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 14:00:00:000',121), 110.0, 123)
insert into StockQuotes values (convert(datetime,'2006-10-01 14:15:00:000',121), 109.0, 432)
insert into StockQuotes values (convert(datetime,'2006-10-01 14:30:00:000',121), 110.0, 345)
insert into StockQuotes values (convert(datetime,'2006-10-01 14:45:00:000',121), 111.0, 456)
insert into StockQuotes values (convert(datetime,'2006-10-01 14:59:59:999',121), 110.0, 756)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 15:00:00:000',121), 109.0, 234)
insert into StockQuotes values (convert(datetime,'2006-10-01 15:15:00:000',121), 110.0, 432)
insert into StockQuotes values (convert(datetime,'2006-10-01 15:30:00:000',121), 110.0, 234)
insert into StockQuotes values (convert(datetime,'2006-10-01 15:45:00:000',121), 111.0, 456)
insert into StockQuotes values (convert(datetime,'2006-10-01 15:59:59:999',121), 110.0, 765)
--
insert into StockQuotes values (convert(datetime,'2006-10-01 16:00:00:000',121), 111.0, 123)
insert into StockQuotes values (convert(datetime,'2006-10-01 16:15:00:000',121), 112.0, 432)
insert into StockQuotes values (convert(datetime,'2006-10-01 16:30:00:000',121), 113.0, 543)
insert into StockQuotes values (convert(datetime,'2006-10-01 16:45:00:000',121), 114.0, 765)
insert into StockQuotes values (convert(datetime,'2006-10-01 16:59:59:999',121), 115.0, 575) --market closing--
-- Daily
insert into StockQuotes values (convert(datetime,'2006-10-02 09:00:00:000',121), 114.0, 654) --market opening--
insert into StockQuotes values (convert(datetime,'2006-10-02 10:00:00:000',121), 113.0, 456)
insert into StockQuotes values (convert(datetime,'2006-10-02 11:00:00:000',121), 114.0, 465)
insert into StockQuotes values (convert(datetime,'2006-10-02 12:00:00:000',121), 115.0, 356)
insert into StockQuotes values (convert(datetime,'2006-10-02 13:00:00:000',121), 116.0, 543)
insert into StockQuotes values (convert(datetime,'2006-10-02 14:00:00:000',121), 117.0, 234)
insert into StockQuotes values (convert(datetime,'2006-10-02 15:00:59:999',121), 118.0, 243)
insert into StockQuotes values (convert(datetime,'2006-10-02 16:59:59:999',121), 117.0, 134) --market closing--
--
insert into StockQuotes values (convert(datetime,'2006-10-03 09:00:00:000',121), 117.0, 534) --market opening--
insert into StockQuotes values (convert(datetime,'2006-10-03 10:00:00:000',121), 116.0, 354)
insert into StockQuotes values (convert(datetime,'2006-10-03 11:00:00:000',121), 117.0, 756)
insert into StockQuotes values (convert(datetime,'2006-10-03 12:00:00:000',121), 118.0, 876)
insert into StockQuotes values (convert(datetime,'2006-10-03 13:00:00:000',121), 119.0, 567)
insert into StockQuotes values (convert(datetime,'2006-10-03 14:00:00:000',121), 120.0, 467)
insert into StockQuotes values (convert(datetime,'2006-10-03 15:00:59:999',121), 121.0, 466)
insert into StockQuotes values (convert(datetime,'2006-10-03 16:59:59:999',121), 122.0, 342) --market closing--
--
insert into StockQuotes values (convert(datetime,'2006-10-04 09:00:00:000',121), 120.0, 534) --market opening--
insert into StockQuotes values (convert(datetime,'2006-10-04 10:00:00:000',121), 119.0, 354)
insert into StockQuotes values (convert(datetime,'2006-10-04 11:00:00:000',121), 121.0, 234)
insert into StockQuotes values (convert(datetime,'2006-10-04 12:00:00:000',121), 124.0, 876)
insert into StockQuotes values (convert(datetime,'2006-10-04 13:00:00:000',121), 129.0, 234)
insert into StockQuotes values (convert(datetime,'2006-10-04 14:00:00:000',121), 129.0, 467)
insert into StockQuotes values (convert(datetime,'2006-10-04 15:00:59:999',121), 128.0, 235)
insert into StockQuotes values (convert(datetime,'2006-10-04 16:59:59:999',121), 127.0, 342) --market closing--


First and last in my case means the first and last tick in a given period (according to the DateTimeStamp column)

For different periods I need to group the data and extract: total volume, highest- lowest- open- and close prices.
The different periods are (weekly - daily - hourly -15min - 5min – 1min), but if I can do it for one period, the other must be relative simple.

So for daily periods the expected output must look like this:
Daily           PriceHigh       PriceLow        PriceOpen       PriceClose      TotVolume   
2006-10-01 127.0000 109.0000 125.0000 115.0000 18096
2006-10-02 118.0000 113.0000 114.0000 117.0000 3085
2006-10-03 122.0000 116.0000 117.0000 122.0000 4362
2006-10-04 129.0000 119.0000 120.0000 127.0000 3276


Any attempt to access the aggregate set in a sub select failed, but I am not so good with SQL so maybe I‘m overlooking something hire. This is one of the things I tried:
/* Daily periods */
select
Convert(Varchar(10), DateTimeStamp, 120) as DailyDate,
Max(Price) as PriceHigh,
Min(Price) as PriceLow,
(select top 1 Price from StockQuotes where DateTimeStamp = DailyDate order by DailyDate) as PriceOpen,
(select top 1 Price from StockQuotes where DateTimeStamp = DailyDate order by DailyDate desc) as PriceClose,
Sum(Volume) as TotVolume
from StockQuotes
group by Convert(Varchar(10), DateTimeStamp, 120)
order by Convert(Varchar(10), DateTimeStamp, 120)

/* Daily periods */
select
Convert(Varchar(10), DateTimeStamp, 120) as DailyDate,
Max(Price) as PriceHigh,
Min(Price) as PriceLow,
(select top 1 Price from StockQuotes where DateTimeStamp = (select Min(DateTimeStamp) from StockQuotes)) as PriceOpen,
(select top 1 Price from StockQuotes where DateTimeStamp = (select Max(DateTimeStamp) from StockQuotes)) as PriceClose,
Sum(Volume) as TotVolume
from StockQuotes
group by Convert(Varchar(10), DateTimeStamp, 120)
order by Convert(Varchar(10), DateTimeStamp, 120)


If anybody has a need way of solving this I would be very grateful.

mvg
Kees
Go to Top of Page

nonono
Starting Member

8 Posts

Posted - 2006-10-04 : 12:41:03
if (exists (select * from sysobjects where name = 'StockQuotes')) drop table StockQuotes

create table StockQuotes
(
SequenceNum int Identity,
DateTimeStamp datetime not null,
Price dec(9,4) not null,
Volume int not null
constraint PK_StockQuotes primary key clustered (SequenceNum)
)
create index IND_StockQuotes on StockQuotes (DateTimeStamp, Price, Volume)

select
distinct Convert(Varchar(10), DateTimeStamp, 120) as DailyDate,
(select max(Price) from StockQuotes where Convert(Varchar(10), DateTimeStamp, 120) = Convert(Varchar(10), d.DateTimeStamp, 120)) as PriceHigh,
(select min(Price) from StockQuotes where Convert(Varchar(10), DateTimeStamp, 120) = Convert(Varchar(10), d.DateTimeStamp, 120)) as PriceLow,
(select top 1 Price from StockQuotes where SequenceNum =
(select min(SequenceNum) from StockQuotes where Convert(Varchar(10), DateTimeStamp, 120) = Convert(Varchar(10), d.DateTimeStamp, 120))) as PriceOpen,
(select top 1 Price from StockQuotes where SequenceNum =
(select max(SequenceNum) from StockQuotes where Convert(Varchar(10), DateTimeStamp, 120) = Convert(Varchar(10), d.DateTimeStamp, 120))) as PriceOpen,
(select sum(Volume) from StockQuotes where Convert(Varchar(10), DateTimeStamp, 120) = Convert(Varchar(10), d.DateTimeStamp, 120)) as TotVolume
from StockQuotes d order by DailyDate

Well, above is a solution, but I'm not so happy with the execution plan (understatement)

It's so stupid and looks so simple the data is right there in the aggregation set, but I can’t find a solution to pick it up.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-05 : 08:01:25
instead of a datetimestamp, why not a datetime? then you wont have to do all that conversion, and you can use neato things like ORDER BY [DATETIME] column, and DATEDIFF.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 08:19:41
[code]SELECT DISTINCT dt.Daily,
dt.PriceHigh,
dt.PriceLow,
f.Price PriceOpen,
l.Price PriceClose,
dt.TotVolume
FROM (
SELECT DATEADD(day, DATEDIFF(day, 0, DateTimeStamp), 0) Daily,
MIN(DateTimeStamp) First,
MAX(DateTimeStamp) Last,
MIN(Price) PriceLow,
MAX(Price) PriceHigh,
SUM(Volume) TotVolume
FROM StockQuotes
GROUP BY DATEADD(day, DATEDIFF(day, 0, DateTimeStamp), 0)
) dt
INNER JOIN StockQuotes f ON f.DateTimeStamp = dt.First
INNER JOIN StockQuotes l ON l.DateTimeStamp = dt.Last[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-05 : 10:05:07
oooo you can do that to a datetimestamp? nice

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 13:56:40
No duplicates for DateTimeStamp ?

kristen
Go to Top of Page

nonono
Starting Member

8 Posts

Posted - 2006-10-05 : 18:50:05
Wow… Peter, you’re the best. This is great! I have had a tiring day today, so I have trouble understanding right now what is going on in your code. But I tested it and it works great. For me the task of solving the other periods, but with your example you helped me a lot. I was already beginning to think I had to solve this problem with tools I better understand.

quote:
Originally posted by DonAtWork

oooo you can do that to a datetimestamp? nice


My words, you said it exactly right!


quote:
Originally posted by Kristen

No duplicates for DateTimeStamp ?


Yes that’s a point, there are duplicates in DateTimeStamp, that’s why I was thinking of adding a sequence number, which can track the data feed. Here is an example of a feed I want to track:
http://tinyurl.com/hzo8s

Peter, thanks again for really looking into my problem. Fantastic!
Go to Top of Page
    Next Page

- Advertisement -