Create Top 3 Values per Group

[Guest]

I'm trying to create a query that shows me the top 3 dates by employee ID. I've converted the date field (CDATE) to a number value (DateNum), and I'm using the following SQL

SELECT [qsel #4].RecID, [qsel #4].EmplID, [qsel #4].CDATE, [qsel #4].CBASIC_PA, [qsel #4].DateNu
FROM [qsel #4], [qsel #4] AS [qsel #4_1
WHERE ((([qsel #4].DateNum) In (SELECT TOP 3 [qsel #4].DateNum FROM [qsel #4] WHERE [qsel #4].[EmplID] = [qsel #4_1].[EmplID] ORDER BY [qsel #4].DateNum DESC)))

Access stops responding. How should I modify the query to get the Top 3 DateNum within each EmplID

 

[Tom Ellison]

Dear Kirk:

I'm not sure I know just what the problem is, but here are some
comments I hope may help.

You have made a cross-product in the FROM clause, I think
unnecessarily.

SELECT RecID, EmplID, CDATE, CBASIC_PA, DateNum
FROM [qsel #4] T
WHERE DateNum) In (SELECT TOP 3 DateNum FROM [qsel #4] T1
WHERE T1.EmplID = T.EmplID ORDER BY DateNum DESC);

As shown, I tend to use very brief aliases for tables and queries. I
also omit showing even these aliases when not necessary, such as in
the main select clause above.

I'm thinking this should work and maybe even give you what you wanted.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Brian Camire]

To add to Tom's post, if CDATE is a Date/Time field, you don't need to
convert it to a number. If it's a Text field, you should probably be using
a Date field instead, or at least using CDate to convert it to a Date/Time
value (in case that's not what you're already doing to get DateNum).

If CDATE is a Date/Time field, you could remove DateNum from the outer
SELECT clause, and replace it with CDATE elsewhere.

I'm trying to create a query that shows me the top 3 dates by employee ID.

I've converted the date field (CDATE) to a number value (DateNum), and I'm
using the following SQL:

SELECT [qsel #4].RecID, [qsel #4].EmplID, [qsel #4].CDATE, [qsel

#4].CBASIC_PA, [qsel #4].DateNum

FROM [qsel #4], [qsel #4] AS [qsel #4_1]
WHERE ((([qsel #4].DateNum) In (SELECT TOP 3 [qsel #4].DateNum FROM [qsel

#4] WHERE [qsel #4].[EmplID] = [qsel #4_1].[EmplID] ORDER BY [qsel
#4].DateNum DESC)));

 

[Tom Ellison]

It's certainly true that the date value needs to be in date/time
format (a numeric, not a text datatype) in order for the ORDER BY
clause to put the rows of the subquery in actual reverse date order,
which could likely be different from "alphabetical" order. As Brian
says, if the DateNum column is text, you may be able to convert it to
a date value. I'm thinking this would be just a change like this:

ORDER BY CDATE(DateNum) DESC

Notice that CDATE() is a function, which is a pretty good reason to
NOT name a column with that same name. However, I believe the syntax
may be unambiguous, so I'm thinking this won't cause a problem for
now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

To add to Tom's post, if CDATE is a Date/Time field, you don't need to
convert it to a number. If it's a Text field, you should probably be using
a Date field instead, or at least using CDate to convert it to a Date/Time
value (in case that's not what you're already doing to get DateNum).

If CDATE is a Date/Time field, you could remove DateNum from the outer
SELECT clause, and replace it with CDATE elsewhere.

I'm trying to create a query that shows me the top 3 dates by employee ID.

I've converted the date field (CDATE) to a number value (DateNum), and I'm
using the following SQL:

SELECT [qsel #4].RecID, [qsel #4].EmplID, [qsel #4].CDATE, [qsel

#4].CBASIC_PA, [qsel #4].DateNum

FROM [qsel #4], [qsel #4] AS [qsel #4_1]
WHERE ((([qsel #4].DateNum) In (SELECT TOP 3 [qsel #4].DateNum FROM [qsel

#4] WHERE [qsel #4].[EmplID] = [qsel #4_1].[EmplID] ORDER BY [qsel
#4].DateNum DESC)));

Access stops responding. How should I modify the query to get the Top 3 DateNum within each EmplID?

 

[Guest]

I would like to thank both Tom and Brian for their help - your suggestions worked perfectly. One final issue: Any reason why this query would run so slowly? I'm using Access 2002 on a P4 computer, and the db is on the C: drive. The query takes 5 minutes to run. The source table for the query only has 2003 records in it

Here's the code - any ideas to improve performance or will I just have to live with it

SELECT EmplID, CDATE, CBASIC_P
FROM [qsel #4] AS
WHERE ((([CDATE]) In (SELECT TOP 3 CDATE FROM [qsel #4] T1 WHERE [T1].[EmplID] = [T].[EmplID] ORDER BY CDATE DESC)))


----- Tom Ellison wrote: ----

It's certainly true that the date value needs to be in date/tim
format (a numeric, not a text datatype) in order for the ORDER B
clause to put the rows of the subquery in actual reverse date order
which could likely be different from "alphabetical" order. As Bria
says, if the DateNum column is text, you may be able to convert it t
a date value. I'm thinking this would be just a change like this

ORDER BY CDATE(DateNum) DES

Notice that CDATE() is a function, which is a pretty good reason t
NOT name a column with that same name. However, I believe the synta
may be unambiguous, so I'm thinking this won't cause a problem fo
now

Tom Elliso
Microsoft Access MV
Ellison Enterprises - Your One Stop IT Expert

On Wed, 3 Dec 2003 12:14:30 -0500, "Brian Camire

To add to Tom's post, if CDATE is a Date/Time field, you don't need t
convert it to a number. If it's a Text field, you should probably be usin
a Date field instead, or at least using CDate to convert it to a Date/Tim
value (in case that's not what you're already doing to get DateNum)

If CDATE is a Date/Time field, you could remove DateNum from the oute

SELECT clause, and replace it with CDATE elsewhere

I'm trying to create a query that shows me the top 3 dates by employee ID

I've converted the date field (CDATE) to a number value (DateNum), and I'
using the following SQL

SELECT [qsel #4].RecID, [qsel #4].EmplID, [qsel #4].CDATE, [qse

#4].CBASIC_PA, [qsel #4].DateNu

FROM [qsel #4], [qsel #4] AS [qsel #4_1
WHERE ((([qsel #4].DateNum) In (SELECT TOP 3 [qsel #4].DateNum FROM [qse

#4] WHERE [qsel #4].[EmplID] = [qsel #4_1].[EmplID] ORDER BY [qse
#4].DateNum DESC)))

 

[Brian Camire]

My guess is that it probably has something to do with whatever "qsel #4" is
doing. Here are some things you might want to look into:

1. Are either of "EmplID" or "CDATE" calculated fields in "qsel #4" (or in
whatever other queries, if any, "qsel #4" is based on)? If so, is there a
way to get equivalent results using another field directly from a table?
Having calculated fields in the WHERE or ORDER BY clauses is generally a lot
slower than having fields directly from a table.

2. Is there a way to simplify "qsel #4", or somehow combine it with this
query?

3. If the fields are directly from a table, does the table define an index
on them? That may help.

Hope this helps.

I would like to thank both Tom and Brian for their help - your suggestions

worked perfectly. One final issue: Any reason why this query would run so
slowly? I'm using Access 2002 on a P4 computer, and the db is on the C:
drive. The query takes 5 minutes to run. The source table for the query
only has 2003 records in it.

Here's the code - any ideas to improve performance or will I just have to live with it?

SELECT EmplID, CDATE, CBASIC_PA
FROM [qsel #4] AS T
WHERE ((([CDATE]) In (SELECT TOP 3 CDATE FROM [qsel #4] T1 WHERE

[T1].[EmplID] = [T].[EmplID] ORDER BY CDATE DESC)));

----- Tom Ellison wrote: -----

It's certainly true that the date value needs to be in date/time
format (a numeric, not a text datatype) in order for the ORDER BY
clause to put the rows of the subquery in actual reverse date order,
which could likely be different from "alphabetical" order. As Brian
says, if the DateNum column is text, you may be able to convert it to
a date value. I'm thinking this would be just a change like this:

ORDER BY CDATE(DateNum) DESC

Notice that CDATE() is a function, which is a pretty good reason to
NOT name a column with that same name. However, I believe the syntax
may be unambiguous, so I'm thinking this won't cause a problem for
now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

To add to Tom's post, if CDATE is a Date/Time field, you don't need to
convert it to a number. If it's a Text field, you should probably be using
a Date field instead, or at least using CDate to convert it to a Date/Time
value (in case that's not what you're already doing to get DateNum).

If CDATE is a Date/Time field, you could remove DateNum from the

outer
SELECT clause, and replace it with CDATE elsewhere.

I'm trying to create a query that shows me the top 3 dates by

employee ID.
I've converted the date field (CDATE) to a number value (DateNum), and I'm
using the following SQL:

SELECT [qsel #4].RecID, [qsel #4].EmplID, [qsel #4].CDATE, [qsel

#4].CBASIC_PA, [qsel #4].DateNum

FROM [qsel #4], [qsel #4] AS [qsel #4_1]
WHERE ((([qsel #4].DateNum) In (SELECT TOP 3 [qsel #4].DateNum

FROM [qsel
#4] WHERE [qsel #4].[EmplID] = [qsel #4_1].[EmplID] ORDER BY [qsel
#4].DateNum DESC)));

Access stops responding. How should I modify the query to get

the Top 3
DateNum within each EmplID?

 

[Tom Ellison]

Dear Kirk:

To optimize performance could be a bit complex, since you're basing
this query on another query. First, you must optimize both queries
(and any other lower-level queries if there is a chain of them).
However, at some point this is based on some table(s) and you'll need
the indexing on those tables that permits the performance you'll need.

I think I'd start by rewriting this so the new query is based directly
on the table(s). Then I suggest you do a quick study of how to
optimize Jet queries by indexing the tables.

Finally, I'd say your query is slow because Jet is slow. It is not
capable of nearly the performance optimization that MSDE has. For
serious development, I have found the switch to MSDE to be
indispensible.

P. S. Maybe its because of a collision between the number of rows in
your source table and the current year. If so, then it should speed
up after Jan 1. Yuk, yuk, yuk!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I would like to thank both Tom and Brian for their help - your suggestions worked perfectly. One final issue: Any reason why this query would run so slowly? I'm using Access 2002 on a P4 computer, and the db is on the C: drive. The query takes 5 minutes to run. The source table for the query only has 2003 records in it.

Here's the code - any ideas to improve performance or will I just have to live with it?

SELECT EmplID, CDATE, CBASIC_PA
FROM [qsel #4] AS T
WHERE ((([CDATE]) In (SELECT TOP 3 CDATE FROM [qsel #4] T1 WHERE [T1].[EmplID] = [T].[EmplID] ORDER BY CDATE DESC)));


----- Tom Ellison wrote: -----

It's certainly true that the date value needs to be in date/time
format (a numeric, not a text datatype) in order for the ORDER BY
clause to put the rows of the subquery in actual reverse date order,
which could likely be different from "alphabetical" order. As Brian
says, if the DateNum column is text, you may be able to convert it to
a date value. I'm thinking this would be just a change like this:

ORDER BY CDATE(DateNum) DESC

Notice that CDATE() is a function, which is a pretty good reason to
NOT name a column with that same name. However, I believe the syntax
may be unambiguous, so I'm thinking this won't cause a problem for
now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

To add to Tom's post, if CDATE is a Date/Time field, you don't need to
convert it to a number. If it's a Text field, you should probably be using
a Date field instead, or at least using CDate to convert it to a Date/Time
value (in case that's not what you're already doing to get DateNum).

If CDATE is a Date/Time field, you could remove DateNum from the outer

SELECT clause, and replace it with CDATE elsewhere.

I'm trying to create a query that shows me the top 3 dates by employee ID.

I've converted the date field (CDATE) to a number value (DateNum), and I'm
using the following SQL:

SELECT [qsel #4].RecID, [qsel #4].EmplID, [qsel #4].CDATE, [qsel

#4].CBASIC_PA, [qsel #4].DateNum

FROM [qsel #4], [qsel #4] AS [qsel #4_1]
WHERE ((([qsel #4].DateNum) In (SELECT TOP 3 [qsel #4].DateNum FROM [qsel

#4] WHERE [qsel #4].[EmplID] = [qsel #4_1].[EmplID] ORDER BY [qsel
#4].DateNum DESC)));

Access stops responding. How should I modify the query to get the Top 3 DateNum within each EmplID?