Selecting only the Top 5

B

Bunky

Aaron,

I am afraid I do not understand where this proposed code is supposed to be
put in the aforementioned queries. Or are you suggesting to re-write them
totally?

a a r o n . k e m p f @ g m a i l . c o said:
sql server supports this

select top 200 with ties
from reservations
order by reservations desc
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


Bunky said:
Karl,

Thank you for all your assistance; it works fine now. I'm sorry I did not
notice your wording. Sleep deprived taking care of wife will do it everytime.

Thanks again.

:

I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level. This will be determined by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names. What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze, Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group = Premier type? What is
Q.Points? Product Q1 I think is the table name but what is Q1.[Group]= to.
I was trying to put names to your example and failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5 maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of reservations booked by premier owners
and I have then sorted by premier type and resort visited. Now I wish to see
only the top 5 resorts these premier owners visited grouped by the premier
type. I'm sure SQL can do it but just putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
G

Gina Whipp

Buky,

Aaron is suggesting (as he always does no matter what the question) that you
migrate to SQL Server. Please disregard his post as it does not apply to
your issue.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Bunky said:
Aaron,

I am afraid I do not understand where this proposed code is supposed to be
put in the aforementioned queries. Or are you suggesting to re-write them
totally?

a a r o n . k e m p f @ g m a i l . c o said:
sql server supports this

select top 200 with ties
from reservations
order by reservations desc
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run
and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between
[Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt
tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3,
Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha
Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE
Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


:

Karl,

Thank you for all your assistance; it works fine now. I'm sorry I
did not
notice your wording. Sleep deprived taking care of wife will do it
everytime.

Thanks again.

:

I'm sorry, did I not explain it well enough when I said "Try these
two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to
run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number])
AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res
number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM
tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is
what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at
broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze
level, S for
Silver level and G for Gold level. This will be determined
by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5
resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but
what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names.
What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3
reservations for type S
followed by proj 22 with 2 reservations and then proj 33
with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of
reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS
Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your
table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze,
Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each
group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while
the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this
is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business
Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners
visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort,
Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group
= Premier type? What is
Q.Points? Product Q1 I think is the table
name but what is Q1.[Group]= to.
I was trying to put names to your example and
failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5
maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*)
FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS
Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] <
Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of
reservations booked by premier owners
and I have then sorted by premier type
and resort visited. Now I wish to see
only the top 5 resorts these premier
owners visited grouped by the premier
type. I'm sure SQL can do it but just
putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer MVP

Try changing the 2nd query to the following. This should include ties.

SELECT Q.Gold AS Expr1
, Q.Proj AS Expr2
, Q.QTY AS Expr3
, Resorts.Resort
, 1 + (SELECT COUNT(*) FROM PremierTDCountqry Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] > Q.[QTY]) AS Rank
, [Ownership Table].[Alpha Ownership]
, [Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter])
INNER JOIN Resorts ON Q.Proj = Resorts.Proj
WHERE 1 + (SELECT COUNT(*) FROM PremierTDCountqry Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] > Q.[QTY])<=5
ORDER BY Q.Gold, Q.QTY DESC;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


Bunky said:
Karl,

Thank you for all your assistance; it works fine now. I'm sorry I did not
notice your wording. Sleep deprived taking care of wife will do it everytime.

Thanks again.

KARL DEWEY said:
I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level. This will be determined by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names. What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze, Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group = Premier type? What is
Q.Points? Product Q1 I think is the table name but what is Q1.[Group]= to.
I was trying to put names to your example and failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5 maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of reservations booked by premier owners
and I have then sorted by premier type and resort visited. Now I wish to see
only the top 5 resorts these premier owners visited grouped by the premier
type. I'm sure SQL can do it but just putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

The reason they both are dropped is that the duplication results in giving
them both a ranking of 6 and that is >5 and not listed.

In the past I have concatenated another field such as primary key autonumber
to look like this --
AND Q1.[QTY] & [AutoKey] >= Q.[QTY] & [AutoKey])

but that might not work for you.

Bunky said:
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


Bunky said:
Karl,

Thank you for all your assistance; it works fine now. I'm sorry I did not
notice your wording. Sleep deprived taking care of wife will do it everytime.

Thanks again.

KARL DEWEY said:
I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level. This will be determined by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names. What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze, Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group = Premier type? What is
Q.Points? Product Q1 I think is the table name but what is Q1.[Group]= to.
I was trying to put names to your example and failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5 maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of reservations booked by premier owners
and I have then sorted by premier type and resort visited. Now I wish to see
only the top 5 resorts these premier owners visited grouped by the premier
type. I'm sure SQL can do it but just putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
B

Bunky

Worked like a champ! Thank you

John Spencer MVP said:
Try changing the 2nd query to the following. This should include ties.

SELECT Q.Gold AS Expr1
, Q.Proj AS Expr2
, Q.QTY AS Expr3
, Resorts.Resort
, 1 + (SELECT COUNT(*) FROM PremierTDCountqry Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] > Q.[QTY]) AS Rank
, [Ownership Table].[Alpha Ownership]
, [Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter])
INNER JOIN Resorts ON Q.Proj = Resorts.Proj
WHERE 1 + (SELECT COUNT(*) FROM PremierTDCountqry Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] > Q.[QTY])<=5
ORDER BY Q.Gold, Q.QTY DESC;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


Bunky said:
Karl,

Thank you for all your assistance; it works fine now. I'm sorry I did not
notice your wording. Sleep deprived taking care of wife will do it everytime.

Thanks again.

:

I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level. This will be determined by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names. What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze, Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group = Premier type? What is
Q.Points? Product Q1 I think is the table name but what is Q1.[Group]= to.
I was trying to put names to your example and failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5 maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of reservations booked by premier owners
and I have then sorted by premier type and resort visited. Now I wish to see
only the top 5 resorts these premier owners visited grouped by the premier
type. I'm sure SQL can do it but just putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
B

Bunky

Karl,

Thanks for the tip. John's code above worked to perfection. I can only
wish that someday I won't have to bother everyone to solve these types of
problems.

kent

KARL DEWEY said:
The reason they both are dropped is that the duplication results in giving
them both a ranking of 6 and that is >5 and not listed.

In the past I have concatenated another field such as primary key autonumber
to look like this --
AND Q1.[QTY] & [AutoKey] >= Q.[QTY] & [AutoKey])

but that might not work for you.

Bunky said:
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


Bunky said:
Karl,

Thank you for all your assistance; it works fine now. I'm sorry I did not
notice your wording. Sleep deprived taking care of wife will do it everytime.

Thanks again.

:

I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level. This will be determined by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names. What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze, Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group = Premier type? What is
Q.Points? Product Q1 I think is the table name but what is Q1.[Group]= to.
I was trying to put names to your example and failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5 maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of reservations booked by premier owners
and I have then sorted by premier type and resort visited. Now I wish to see
only the top 5 resorts these premier owners visited grouped by the premier
type. I'm sure SQL can do it but just putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
A

aaron.kempf

put select top 100 with ties in your query (the SQL window)

if your database engine doesn't support it.. then the database that
you're attempting to use-- doesn't mean your needs.
you shoudl upsize to SQL Server.. it's free, it's easier, faster, more
stable, more scalable.. and more popular






Aaron,

I am afraid I do not understand where this proposed code is supposed to be
put in the aforementioned queries.  Or are you suggesting to re-write them
totally?

:


sql server supports this
Click to expand...
select top 200 with ties
from reservations
order by reservations desc
Click to expand...
Bunky said:
Hi Karl!
One problem has arisen that I did not anticipate.  When the queriesrun and
a tie occurs, it does not return anything of the tie.  Example:
 Mbrs     Resort      # of Reservations
Gold        Spa1            345
Gold        Spa3            258
Gold        Spa89          125
Gold        Spa23            89
Gold        Spa9              65
Gold        Spa87            65
If this situation it drops the last 2 -  is there a way to show thetie
regardless of the ranking?
Here is the code.
1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;
2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1       WHERE Q1.[Gold] = Q.[Gold]
      AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1       WHEREQ1.[Gold] =
Q.[Gold]         AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;
:
Karl,
Thank you for all your assistance; it works fine now.  I'm sorry I did not
notice your wording.  Sleep deprived taking care of wife will do it everytime.
Thanks again.
:
I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little
:
When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?
:
Try these two queries -
     tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;
SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
      WHERE Q1.[Gold] = Q.[Gold]
        AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
      WHERE Q1.[Gold] = Q.[Gold]
        AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;
--
KARL DEWEY
Build a little - Test a little
:
Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.
Table name is tblReswithGold
I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels.  ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level.  This will be determined by counting the
res numbers for each proj.
OK?
:
Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort?  Is 'Proj' your 'premier owners'?
Re-state your requirement in terms of the fields names.  What is the table
name.
--
KARL DEWEY
Build a little - Test a little
:
Table Structure
Res-Date       Date/Time
Proj              Number
Res Number  Text
Gold             Text
Sample Date
Res-Date       Proj      Res Number      Gold
9/1/2008         63         QNP3W              S
9/1/2008         22         QNP3Z               S
9/3/2008         33         ZXZXZ               S
9/3/2008         63         XZXZX               S
9/5/2008         63         YTYTY               S
9/5/2008         22         UYUYU               S
So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.
Does this help?
:
UNTESTED  - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count ofreservations], (SELECT
COUNT(*) FROM YourTable Q1
      WHERE Q1.[Type] = Q.[Type]
        AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;
If this does not work then I need you to post your table structure showing
field names and datatype.  Also post sample data.
--
KARL DEWEY
Build a little - Test a little
:
The Type refers to ownership type.  We have Bronze, Silver, Gold, and
Platinum.  Mgmt wants to know the top 5 rsts eachgroup goes to.  So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05.  All of this is determined by the
number of reservations booked.
:
You have Spa15 as both type A and B.  Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little
:
You are close.  But this is what I need
Type      Resort     Count of reservations
A            Spa1              98
A            Spa21            87
A            Spa13            45
A            Spa74            24
A            Spa15            15
B            Spa74           105
B            Spa66             56
B            Spa89             32
B            Spa21             16
B            Spa15             11
Thanks for your assistance.
:
Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type   Resort
A        Spa1     15
A        Spa2       9
B         Ski1       6
C         Casino1  5
B         Ski2       3
    Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;
Click to expand...
Click to expand...

...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...
 
G

George

Please don't let Mr. Kempf upset you. His goal is not to help people; his
goal is to disrupt the newsgroups. His method is to assert "SQL Server"
regardless of the question.

Let's give Karl a chance to address your REAL question.

Thanks.




Bunky said:
Aaron,

I am afraid I do not understand where this proposed code is supposed to be
put in the aforementioned queries. Or are you suggesting to re-write them
totally?

a a r o n . k e m p f @ g m a i l . c o said:
sql server supports this

select top 200 with ties
from reservations
order by reservations desc
Hi Karl!

One problem has arisen that I did not anticipate. When the queries run
and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65

If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?

Here is the code.

1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between
[Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt
tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;

2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3,
Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha
Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE
Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;


:

Karl,

Thank you for all your assistance; it works fine now. I'm sorry I
did not
notice your wording. Sleep deprived taking care of wife will do it
everytime.

Thanks again.

:

I'm sorry, did I not explain it well enough when I said "Try these
two
queries"?
--
KARL DEWEY
Build a little - Test a little


:

When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to
run
together?


:

Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number])
AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res
number]) DESC;

SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM
tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;

--
KARL DEWEY
Build a little - Test a little


:

Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is
what I am counting.

Table name is tblReswithGold

I wish to see the top 5 Proj's that Gold are staying at
broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze
level, S for
Silver level and G for Gold level. This will be determined
by counting the
res numbers for each proj.

OK?

:

Your orignal post read "Now I wish to see only the top 5
resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but
what field is the
Resort? Is 'Proj' your 'premier owners'?

Re-state your requirement in terms of the fields names.
What is the table
name.

--
KARL DEWEY
Build a little - Test a little


:

Table Structure

Res-Date Date/Time
Proj Number
Res Number Text
Gold Text

Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S

So, I want to see that proj 63 is the top with 3
reservations for type S
followed by proj 22 with 2 reservations and then proj 33
with 1 reservation.
Then find the top 5 for the other Gold types as well.

Does this help?


:

UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of
reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS
Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;

If this does not work then I need you to post your
table structure showing
field names and datatype. Also post sample data.

--
KARL DEWEY
Build a little - Test a little


:

The Type refers to ownership type. We have Bronze,
Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each
group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while
the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this
is determined by the
number of reservations booked.

:

You have Spa15 as both type A and B. Business
Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little


:

You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11

Thanks for your assistance.

:

Forget my earlier post.
the top 5 resorts these premier owners
visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3

Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort,
Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;

--
KARL DEWEY
Build a little - Test a little


:

Karl,

I am not following the SQL code. Is Q.Group
= Premier type? What is
Q.Points? Product Q1 I think is the table
name but what is Q1.[Group]= to.
I was trying to put names to your example and
failed miserably. My bad- too
many years since I really coded SQL!

:

Use a ranking query with rank set to 5
maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*)
FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS
Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] <
Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls a total of
reservations booked by premier owners
and I have then sorted by premier type
and resort visited. Now I wish to see
only the top 5 resorts these premier
owners visited grouped by the premier
type. I'm sure SQL can do it but just
putting 5 in at the top of the query
will not get my results. Ideas??

Thank you for your assistance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Only return top 5 records per category 1
Top 5 by specialty in subquery 1
Top 5 SQL in an Aggregate Query 2
Top 10 Report 2
Adding a percentage to a Top 5 query 1
Selecting top 5 2
top 5% 4
Top 5 3

Top