SQL Alert: limit

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) * ...

DECLARE @.cnt int

SET @.cnt = 5

SELECT TOP (@.cnt) * ...

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.

Hello,

We are using Access ADP as a front end, SQL Server 2000 as a back end.
We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

For example, Eastern Sales Group can see clients located in their
region, but they cannot see clients located in the Northern Region.

Is there an elegant way to do this? Below a two solutions which have
been proposed, but none seem to fit. Access is required as a Front End
for its ease of use.

*********
** 1 **
*********
Add an additional attribute (bit mask value) to tblCustomers, and
query appropriately based on the user's group membership.

Problem:
Access allows users direct access to the underlying table.

*********
** 2 **
*********
Create a separe table for each group (effectively splitting
tblCustomers into smaller, separate tables based on group access).
Then, apply SQL Server security on the objects to enforce the business
rules.

Problem:
Does this break Normal Form? I've never seen a solution like this.

I've googled and found similar questions, but not a good solution.

Any suggestions would be appreciated.

Thanks,
Jeff
Jeffrey Walton
noloader.at.yahoo.com"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0404231447.777fe29c@.posting.google.c om...
> Hello,
> We are using Access ADP as a front end, SQL Server 2000 as a back end.
> We have a customer contact database. We would like to limit certain
> users to only receive certain records based on Windows NT group
> membership.
> For example, Eastern Sales Group can see clients located in their
> region, but they cannot see clients located in the Northern Region.
> Is there an elegant way to do this? Below a two solutions which have
> been proposed, but none seem to fit. Access is required as a Front End
> for its ease of use.
> *********
> ** 1 **
> *********
> Add an additional attribute (bit mask value) to tblCustomers, and
> query appropriately based on the user's group membership.
> Problem:
> Access allows users direct access to the underlying table.
> *********
> ** 2 **
> *********
> Create a separe table for each group (effectively splitting
> tblCustomers into smaller, separate tables based on group access).
> Then, apply SQL Server security on the objects to enforce the business
> rules.
> Problem:
> Does this break Normal Form? I've never seen a solution like this.
> I've googled and found similar questions, but not a good solution.
> Any suggestions would be appreciated.
> Thanks,
> Jeff
> Jeffrey Walton
> noloader.at.yahoo.com

In general, there is no reason why your users should need to access tables
directly. If you manage data access through stored procedures, then the
procedures can check role membership and only return or update those rows
which are permitted to the user. You could also use views, based on role
membership, or use application roles and handle everything in the client
application.

I would avoid solution 2, because it duplicates data - whatever information
you use to partition the data could also be put in a column. But if you have
a large amount of data, and if users only access their own data, then this
might also be an option.

Simon|||RE/
>We have a customer contact database. We would like to limit certain
>users to only receive certain records based on Windows NT group
>membership.

Based on what little experience I've had doing an Access front end against SQL
Server, I'd set SQL Server security so that nobody and nothing can get directly
to the tables in question and then develop stored procedures and/or views to
move the data back-and-forth between tables and app.
--
PeteCresswell

Tuesday, March 6, 2012

Access to MSDE database

Thursday, February 16, 2012

Access' Last equivalent in SQL2005

Monday, February 13, 2012

Access Front End and SQL Server 2000 Record Level Access Control

SQL Alert

Blog Archive

About Me