Access' Last equivalent in SQL2005

Microsoft Access has the functions - First & Last that you can apply to a select list to limit the returned rows to those. I need to convert several queries, developed by a data researcher in Access, that use these functions into genuine Transact SQL.

I can use T-SQL's Top for the First function, but I'm stumped on what to use for Last.

Also, the inherited queries don't have any 'order' clauses, wouldn't they be necessary when using such a function?

Thanks.

For a 'q&d' (quick and dirty') way to accomplish your task, for LAST, just like using the TOP 1 function for the FIRST, you can reverse the sort [ORDER BY DESC] and retrieve the TOP 1 for the LAST.

And a custom function may be more efficient -it depends upon several factors. If you posted your entire query, someone here may be able to provide more directed assistance.

|||

This is the one I'm working on now(first), it's designed to show which students didn't finish school but were pretty close to a degree. It's nasty.

SELECT Last(fice_table.inst_type) AS LastOfinst_type, student_table.academic_year, Max(student_table.term) AS term, Last(student_table.geo_state) AS LastOfgeo_state, student_table.ssn_id, Last(student_table.degree_intent) AS LastOfdegree_intent, Last(student_table.student_level) AS LastOfstudent_level, graduated_student_table.ssn_id, Last(stdnt_lvl_desc_table.description) AS StudentLevel, Last(degr_intnt_desc_table.description) AS DegreeIntent

FROM (((student_table INNER JOIN fice_table

ON student_table.fice_code = fice_table.fice_code)

LEFT JOIN graduated_student_table

ON student_table.ssn_id = graduated_student_table.ssn_id)

INNER JOIN degr_intnt_desc_table

ON (student_table.academic_year = degr_intnt_desc_table.academic_year) AND (student_table.degree_intent = degr_intnt_desc_table.degree_intent))

INNER JOIN stdnt_lvl_desc_table

ON (student_table.academic_year = stdnt_lvl_desc_table.academic_year) AND (student_table.student_level = stdnt_lvl_desc_table.code)

GROUP BY student_table.academic_year, student_table.ssn_id, graduated_student_table.ssn_id

HAVING (((Last(fice_table.inst_type))="1") AND

((student_table.academic_year)="2006" And Not (student_table.academic_year)="2007") AND

((Last(student_table.geo_state))="04") AND

(Not (Last(student_table.degree_intent))="3") AND

((Last(student_table.student_level))="01" Or (Last(student_table.student_level))="02" Or (Last(student_table.student_level))="03" Or (Last(student_table.student_level))="04") AND

((graduated_student_table.ssn_id) Is Null));

And since she is using 'Last' without any ordering, I'm now wondering if the thing has ever worked correctly.

Thanks for your time.

|||

That is an interesting query. There is a bit of difference between Access and SQL Server. Access is a record manager and tables do have some order, so it might not be a problem. In SQL Server, it is much more particular about things like this. SQL Server optimizes query plans in a very strict manner.

|||

Interesting that's a kind word for it

I tried using top with descending indices, it was a disaster. I wound up with 1 row. Access seems able to stack up the Last functions, while SQL only allows a single TOP.

This is looking like it will have to be done with a program or stay in Access. It's beyond my meager T-SQL abilities.

Thanks.

|||

You can specify the number of rows TOP returns

SELECT TOP (5) * ...

or

DECLARE @.cnt int

SET @.cnt = 5

SELECT TOP (@.cnt) * ...

or

SELECT TOP (10) PERCENT * ...

|||

Paul,

What determines the 'LAST' entry in each of the tables?

(Is there a datetime column in each table?)

It would be helpful to have the table DDL...

|||

I'm not sure what determines 'Last' of each column. That lack of ordering was the basis of my questioning the whole history of the use of this query.

But generally, the columns that are 'Last'ed are char(1 or 2) with strictly numeric data. e.g. the domain of fice_table.inst_type is '1', '2', '3', '4' but there is no index on that column. Likewise with student_table.geo_state, the type is char(2) with domain of '00' -> '50' inclusive & '98' & '99' again no index on that column.

And I know that I can specify TOP x <col name>, but I need the Last one of each group by and I can't figure out how to do that. I've been stumped forever by a simple query to get the top 5 scores from each county from a table with a county code column domain of '00' to '75' and an integer score column. I know it can be done and it has to be the grouping that's the trouble.

|||

If you want to post some sample DDL (create table, etc.) and sample data (preferrablyin the form of INSERT statements)...we can all take a stab at coming up with something that works for you.

|||

You should be able to use the row_number() function to some advantage here. Elsewhere you asked about finding the top 5 rows for each student, which you can also do this way.

WITH Ranked AS (
select
<the columns you need>,
row_number() over (
partition by
student_table.academic_year,
student_table.ssn_id,
graduated_student_table.ssn_id
order by
whatever column list will put the rows
you want for each student/year at the top
) as rk
from your join
), TopFiveEachStudentEachYear AS (
select * from Ranked
where rk <= 5
)
select * from TopFiveEachStudentEachYear
-- or a different query against these results

The ordering in the OVER clause may benefit from expressions like:

ORDER BY

student_table.student_level DESC,

case when student table_academic_year = '2006' then 0 else 1 end,

...

Steve Kass

Drew University

www.stevekass.com

|||

OK, here's the DDL, I'm cutting out a LOT of columns and deleting a bunch of indices (it's a huge database):

This first table is kind of the parent of all tables underneath, one row per college:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[fice_table] Script Date: 07/03/2007 14:00:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fice_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[fice_table](
[fice_code] [char](6) NOT NULL,
[school_name] [varchar](62) NOT NULL,
[school_abbr] [varchar](8) NOT NULL,
[school_type] [char](1) NULL,
[inst_type] [char](1) NULL,
[act_code] [char](4) NULL,
[fap_code] [char](3) NULL,
[ipeds_code] [char](6) NULL,
[type_code] [char](1) NULL,
[sreb_cat] [char](2) NULL,
[ets_code] [char](4) NULL,
[system_code] [char](1) NULL,
[title_iv_code] [char](6) NULL,
CONSTRAINT [PK_fice_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO

This is the student_table, one row per student per term per year per college, so a student can have up to 4 rows per year 0 = Summer II, 1 = Fall, 2 = Spring, 3 = Summer I

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[term] [char](1) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[geo_county] [char](3) NULL,
[geo_state] [char](2) NULL,
...
[degree_intent] [char](1) NULL,
[attend_status] [char](1) NULL,
[init_admit_date] [char](6) NULL,
[student_level] [char](2) NULL,
...
[diploma_ged] [char](1) NULL,
...
[on_cr_hours] [tinyint] NULL,
[off_cr_hours] [tinyint] NULL,
...
) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [PK_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'PK_student_table')
ALTER TABLE [dbo].[student_table] ADD CONSTRAINT [PK_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
END
GO
SET ANSI_PADDING OFF
GO

/****** Object: Index [_dtw_index_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'_dtw_index_student_table')
CREATE NONCLUSTERED INDEX [_dtw_index_student_table] ON [dbo].[student_table]
(
[academic_year] ASC,
[term] ASC,
[fice_code] ASC
)
INCLUDE ( [student_level],
[on_cr_hours]) ...
GO

/****** Object: Index [fice_ssn_admit_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_admit_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_admit_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[admit_status] ASC,
[hs_grad_year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_ssn_enroll_status_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_enroll_status_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_enroll_status_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[enroll_status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_year_term_ssn_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_year_term_ssn_idx')
CREATE NONCLUSTERED INDEX [fice_year_term_ssn_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
GO

Now here is the graduated_student_table, again lot's cut out:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[graduated_student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[graduation_date] [char](6) NULL,
[degree_level] [char](2) NULL,
...

[init_enroll_status] [char](1) NULL,
[init_attend_status] [char](1) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
...

/****** Object: Index [PK_graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND name = N'PK_graduated_student_table')
ALTER TABLE [dbo].[graduated_student_table] ADD CONSTRAINT [PK_graduated_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[ssn_id] ASC,
[degree_1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
GO

Now here is the stdnt_lvl_desc_table, there are codes for each student level, 01 freshman, 02 sophomore, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[stdnt_lvl_desc_table] Script Date: 07/03/2007 13:58:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stdnt_lvl_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[stdnt_lvl_desc_table](
[academic_year] [char](4) NOT NULL,
[code] [char](2) NOT NULL,
[description] [varchar](40) NOT NULL,
CONSTRAINT [PK_stdnt_lvl_desc_table] PRIMARY KEY NONCLUSTERED
(
[academic_year] ASC,
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

And the degree_intnt_desc_table:

There are codes for the reason the student began college, 1 thru 9 for Bacc Degree Seeking, Assoc Degree Seeking, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[degr_intnt_desc_table] Script Date: 07/03/2007 13:59:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[degr_intnt_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[degr_intnt_desc_table](
[academic_year] [char](4) NOT NULL,
[degree_intent] [char](1) NOT NULL,
[description] [varchar](50) NULL,
[include_in_fall_cohort] [char](1) NULL,
CONSTRAINT [PK_degr_intnt_desc_table] PRIMARY KEY CLUSTERED
(
[academic_year] ASC,
[degree_intent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

As for sample data, I'm afraid I can't let that out and my VSDBPro project isn't finished yet. But, you can imagine that each row in the student_table has a Social Security Number or College Assigned ID and the graduated_student_table has a corresponding ssn_id for graduates.