Use top value in a query

[John Baker]

Can anyone tell me if it is possible to use the top value (which is
available on the menu in design view of the menu) and be able to enter it as
a parameter of the query?

John B

 

[Baz]

Can anyone tell me if it is possible to use the top value (which is
available on the menu in design view of the menu) and be able to enter it as
a parameter of the query?

John B

No, you can't parameterize Top. You would need to build the query
dynamically using VBA before running or opening it or whatever you are
seeking to do.

 

[Jamie Collins]

No, you can't parameterize Top.

If the OP rewrites their query, replacing the proprietary TOP N syntax
with a standard SQL construct, then they could. See

http://groups.google.com/group/microsoft.public.access.queries/msg/2ca7a15954815edb

Jamie.

--

 

[Smartin]

If the OP rewrites their query, replacing the proprietary TOP N syntax
with a standard SQL construct, then they could. See

http://groups.google.com/group/microsoft.public.access.queries/msg/2ca7a15954815edb

Jamie.

Interesting. As long as the OP understands the difference between the
proprietary version does (return a subset of values) Vs. what your
example does (return a specific number of rows)...

I'm adding your example to my library though. Thanks!

 

[Jamie Collins]

Interesting. As long as the OP understands the difference between the
proprietary version does (return a subset of values) Vs. what your
example does (return a specific number of rows)...

Good point. I probably linked to the wrong thread

Here's an example that is a better equivalent of the proprietary TOP N
syntax in that it returns a resultset:

CREATE TABLE Test2 (
data_col INTEGER NOT NULL)
;
INSERT INTO Test2 VALUES (55)
;
INSERT INTO Test2 VALUES (56)
;
INSERT INTO Test2 VALUES (57)
;
INSERT INTO Test2 VALUES (56)
;
INSERT INTO Test2 VALUES (57)
;
CREATE PROCEDURE Test2Proc
(arg_top INTEGER = 1)
AS
SELECT T1.data_col
FROM Test2 AS T1
WHERE arg_top >= (
SELECT COUNT(*) + 1
FROM Test2 AS T2
WHERE T1.data_col < T2.data_col)
;
EXEC Test2Proc 3
;

Jamie.

--

 

[Baz]

Good point. I probably linked to the wrong thread

Here's an example that is a better equivalent of the proprietary TOP N
syntax in that it returns a resultset:

CREATE TABLE Test2 (
data_col INTEGER NOT NULL)
;
INSERT INTO Test2 VALUES (55)
;
INSERT INTO Test2 VALUES (56)
;
INSERT INTO Test2 VALUES (57)
;
INSERT INTO Test2 VALUES (56)
;
INSERT INTO Test2 VALUES (57)
;
CREATE PROCEDURE Test2Proc
(arg_top INTEGER = 1)
AS
SELECT T1.data_col
FROM Test2 AS T1
WHERE arg_top >= (
SELECT COUNT(*) + 1
FROM Test2 AS T2
WHERE T1.data_col < T2.data_col)
;
EXEC Test2Proc 3
;

Jamie.

Correlated subqueries are, of course, a performance disaster in Access.

The OP will doubtless make up his own mind whether he is bothered about
proprietary syntax. Personally I couldn't care less.

 

[Jamie Collins]

Correlated subqueries are, of course, a performance disaster in Access.

The OP will doubtless make up his own mind whether he is bothered about
proprietary syntax. Personally I couldn't care less.

The raison d'etre for this thread is the OP can't use the proprietary
syntax because the value N in TOP N cannot be passed as a parameter
e.g.

CREATE PROCEDURE Test2Proc
(arg_top INTEGER = 1)
AS
SELECT TOP arg_top data_col
FROM Test2
ORDER BY data_col DESC;

causes an error. You care about valid syntax and avoiding errors, right
<g>?

Jamie.

--

 

[Jamie Collins]

Correlated subqueries are, of course, a performance disaster in Access.

The OP will doubtless make up his own mind whether he is bothered about
proprietary syntax. Personally I couldn't care less.

You remind me of this

'I Will Never Have To Port This Code': Debunking shortcuts and SQL
myths
By Joe Celko
http://www.intelligententerprise.com/030422/607celko1_1.jhtml

"If you hang around the database developer newsgroups, you'll find
rookie programmers who write their code with proprietary features of
whatever SQL product they have. They argue that they'll never have to
port this code, so why bother to write standard SQL when you can gain
some advantage in performance with a proprietary feature?"

Jamie.

--

 

[John Spencer]

Jamie,

Seeking enlightenment.

Your example uses unique values. What happens when the values aren't unique
(and there are potential ties for the last position). It seems to me that
all the tied values would be dropped. So if the percentage worked out to
select 10 records and there was a tie for the tenth position, it seems the
query would return only 9 records.

If you change the "greater than or equal" to "greater than", it will return
ties.

SELECT T1.data_col
FROM Test AS T1
WHERE 10 >= (
SELECT COUNT(*)
FROM Test AS T2
WHERE T1.data_col > T2.data_col
)

So, is there a way to modify this to return exactly 10 records? The only
way I can see is to add additional criteria to the ranking sub-query.

 

[Jamie Collins]

Your example uses unique values. What happens when the values aren't unique
(and there are potential ties for the last position). It seems to me that
all the tied values would be dropped. So if the percentage worked out to
select 10 records and there was a tie for the tenth position, it seems the
query would return only 9 records.

If you change the "greater than or equal" to "greater than", it will return
ties.

I'm *really* regretting posting to that thread now <g>. I think I got
it right in the more appropriate example in this thread, which doesn't
use unique values.

is there a way to modify this to return exactly 10 records? The only
way I can see is to add additional criteria to the ranking sub-query.

Agreed, otherwise what criteria would the dumb machine use to pick the
10 records: chronological order of insertion/update, PK order, random?

Jamie.

--

 

[Baz]

The raison d'etre for this thread is the OP can't use the proprietary
syntax because the value N in TOP N cannot be passed as a parameter
e.g.

CREATE PROCEDURE Test2Proc
(arg_top INTEGER = 1)
AS
SELECT TOP arg_top data_col
FROM Test2
ORDER BY data_col DESC;

causes an error. You care about valid syntax and avoiding errors, right
<g>?

Jamie.

I already gave the OP an answer.

Perhaps you have never tried running correlated subqueries against large
tables in Access. When I say it's a performance disaster, I don't mean it's
just a bit slow, I'm talking about the kind of performance where you start
your query running at 5:30 in the evening and hope that it's finished when
you arrive the next morning.

 

[Baz]

You remind me of this

'I Will Never Have To Port This Code': Debunking shortcuts and SQL
myths
By Joe Celko
http://www.intelligententerprise.com/030422/607celko1_1.jhtml

"If you hang around the database developer newsgroups, you'll find
rookie programmers who write their code with proprietary features of
whatever SQL product they have. They argue that they'll never have to
port this code, so why bother to write standard SQL when you can gain
some advantage in performance with a proprietary feature?"

Jamie.

I'm familiar with Celko's pompous utterings, he sprang to my mind when I saw
your original post ;-). However, unlike Celko, this rookie programmer lives
in the real world (and has been doing so for all his 23 years of developing
database systems). One thing that I have seen many times over those decades
is IT purists on a mission who spend huge quantities of their employers'
money chasing theoretical ideals whilst users get ever more frustrated
because nothing ever gets delivered. This generally leads to the users
hacking their own systems together piecemeal in Excel or Access, so the
purists' determination to construct the world's most beautiful systems
actually results in the complete opposite. And invariably they all
eventually get sacked when the company gets fed up with pouring dosh into
the IT department's bottomless pit.

 

[Jamie Collins]

Perhaps you have never tried running correlated subqueries against large
tables in Access. When I say it's a performance disaster, I don't mean it's
just a bit slow, I'm talking about the kind of performance where you start
your query running at 5:30 in the evening and hope that it's finished when
you arrive the next morning.

A bit of advice for you Baz: slagging off the product doesn't win you
any friends in this group ;-)

As regards your proposal to the OP, my experience of applications that
rely on dynamic SQL can be précised as, "Maintenance disaster".

Jamie.

--

 

[Baz]

Perhaps you have never tried running correlated subqueries against large
tables in Access. When I say it's a performance disaster, I don't mean it's
just a bit slow, I'm talking about the kind of performance where you start
your query running at 5:30 in the evening and hope that it's finished when
you arrive the next morning.

A bit of advice for you Baz: slagging off the product doesn't win you
any friends in this group ;-)

As regards your proposal to the OP, my experience of applications that
rely on dynamic SQL can be précised as, "Maintenance disaster".

Jamie.

--

 

[Baz]

A bit of advice for you Baz: slagging off the product doesn't win you
any friends in this group ;-)

Thanks, but when I need your advice I'll ask for it.

How can telling the truth about the product be construed as slagging it off?
Do you think it would be helpful to pretend that the product is perfect when
it clearly isn't?

As regards your proposal to the OP, my experience of applications that
rely on dynamic SQL can be précised as, "Maintenance disaster".

That could be argued about 'til the cows come home, but really the OP has no
other option. For the reasons I have already given, any solution relying on
correlated subqueries is completely impracticable unless the OP's tables are
very small.

 

[Baz]

A bit of advice for you Baz: slagging off the product doesn't win you
any friends in this group ;-)

As regards your proposal to the OP, my experience of applications that
rely on dynamic SQL can be précised as, "Maintenance disaster".

Jamie.

Sorry for the spurious post, dunno how that happened.

 

[Jamie Collins]

Thanks, but when I need your advice I'll ask for it.

How can <<snipped>>

If you won't take advice from me, what about from the Access mvps?

http://www.mvps.org/access/netiquette.htm

"g. Look for Smileys , grins <g>, and other "Emoticons".
"i. When you see one, the preceding statement was not meant to be
really serious, don't take it as such."

That could be argued about 'til the cows come home

Welcome to Usenet!

Jamie.

--