Last & First SQL aggregate functions

I'm trying to migrate an app. from Access to SQL Server, and find that Transact-SQL does not support LAST/FIRST functions. Is there any alternative to these?

Below is the Access SQL statement:

SELECT Last(tblZoneNameString.Val) AS strZoneName, tblZoneNameString.TagIndex
FROM tblZoneNameString
GROUP BY tblZoneNameString.TagIndex
HAVING (((tblZoneNameString.TagIndex)>0));

Use MIN and MAX

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

In SQL Server 2005, you can do something like this: select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex from ( select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex, rank() over (partition by tblZoneNameString.TagIndex order by ?) as rk from tblZoneNameString ) as T where rk = 1 Where I've written ? you will need to put whatever column or columns answer the question "last in order of what?". Perhaps this is something like someDateTime DESC. Steve Kass Drew University JimNolandCBI@.discussions.microsoft.com wrote:
> I'm trying to migrate an app. from Access to SQL Server, and find that
> Transact-SQL does not support LAST/FIRST functions. Is there any
> alternative to these?
>
> Below is the Access SQL statement:
>
> SELECT Last(tblZoneNameString.Val) AS strZoneName,
> tblZoneNameString.TagIndex
> FROM tblZoneNameString
> GROUP BY tblZoneNameString.TagIndex
> HAVING (((tblZoneNameString.TagIndex)>0));
>
>

Large view problem

I have a considerably large view that is pulling data from tables and other view, using user defined functions, and using case statements. This view is taking a lot of time to load.

I was under the impression that SQL server keeps the views uptodate, so selecting data from them is as fast as selecting from a table. It now seems like SQL server rebuilds the view evertime something accesses it.

Can someone please tell me more on this. I am now having to re-write everything :-(

Thanks Jag

Shameless bump|||Do you have indexes on the columns being referenced in the view? Are you filtering the view with a WHERE clause and do they also have indexes and how up to date are they?|||

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

The view is being filtered by a where clause. However, the problem is actually building the view. When I use the query analyzer to select all the rows, it still takes 30-40 seconds to return just 252 rows !!!!

What was happening was that I was using a number of view to pull out the last data being entered into different areas. These view where then combined into this large view. I also used user defined functions to pull out other data.

I have had to change this to use triggers and store the data elsewhere. This has cut the time down considerable, but I an still wondering why the problem existed in the first place.

|||

jagdipa:

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

Perhaps its time to look into it now..There are some very good articles @.http://www.sql-server-performance.com/articles.asp