Breaking ties in a Top n Query

[S Davis]

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12 values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S

 

[S Davis]

FWIW, I am currently using this SQL:

SELECT *
FROM InsFT AS T1
WHERE (((T1.[EDDate]) In (SELECT TOP 12 [EDDate]
FROM InsFT AS T2
WHERE T2.Bus = T1.Bus
ORDER BY [EDDate])));

Which works, but returns more than 12 values if the last results are
tied.

Thanks again
-S

 

[Roger]

Try this

your second select is overridding your subquery

SELECT top 12 *
FROM InsFT AS T1
WHERE (((T1.[EDDate]) In (SELECT TOP 12 [EDDate]
FROM InsFT AS T2
WHERE T2.Bus = T1.Bus
ORDER BY [EDDate])));

FWIW, I am currently using this SQL:

SELECT *
FROM InsFT AS T1
WHERE (((T1.[EDDate]) In (SELECT TOP 12 [EDDate]
FROM InsFT AS T2
WHERE T2.Bus = T1.Bus
ORDER BY [EDDate])));

Which works, but returns more than 12 values if the last results are
tied.

Thanks again
-S

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12 values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S

 

[David F Cox]

There have been many examples on this newsgroup and others of setting a
custom sequence number. If you generate a query to do that based on your
sorted query and select the first twelve records from that. Sorry that I do
not have time to look out the full info at the moment.

David F. Cox

 

[John Spencer]

I see no primary key that could be used to break the ties. DO you have a
primary key in InsFT? IF so what is the primary key field? You are going
to be forced to use that in the subquery. You have to have some way to
distinguish the 12th record from the 13th record. If everything in the two
records is identical, then there is no way that I know of (beyond writing
some VBA code to assign values to a new field) to work this out.

Can you add a field to your table? Add an autonumber field to the table and
that can be used to break the ties.

FWIW, I am currently using this SQL:

SELECT *
FROM InsFT AS T1
WHERE (((T1.[EDDate]) In (SELECT TOP 12 [EDDate]
FROM InsFT AS T2
WHERE T2.Bus = T1.Bus
ORDER BY [EDDate])));

Which works, but returns more than 12 values if the last results are
tied.

Thanks again
-S

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12 values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S

 

[KARL DEWEY]

Ok, I have now translated it for you --

SELECT T.Bus, T.EDDate, T.NextID, (SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate) AS Rank
FROM InsFT AS T
WHERE ((((SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate))<=12))
ORDER BY T.Bus, T.EDDate DESC;

 

[Gary Walter]

PMFBI

I think the Rank "tie-breaking" would involve
you choosing at least one other field and
adding an OR stmt to your WHERE clause,
for example...

(SELECT COUNT(*)
FROM [InsFT] T1
WHERE
(
(T1.Bus = T.Bus)
AND
(
(T1.EDDate > T.EDDate)
OR
(T1.EDDate = T.EDDate
AND
T1.NextID > T.NextID)
)
)
) + 1 AS Rank

That will arbitrarily break a tie if
the records that are tied have different
values for NextID. You know your data.
If NextID won't do it, then please follow
John's advice.

Apologies again for butting in
(especially if I misunderstood).

Ok, I have now translated it for you --

SELECT T.Bus, T.EDDate, T.NextID, (SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate) AS Rank
FROM InsFT AS T
WHERE ((((SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate))<=12))
ORDER BY T.Bus, T.EDDate DESC;

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12 values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S

 

[S Davis]

Nope this is all good info! It was a bit quirky, but I essentially ran
my first query as a subquery, and then ran Karl Dewey's quirky on top
of that with a final query just to order the data. Seperating the steps
seems to have allowed access to think a bit quicker, although the total
refresh time is now 11 minutes. Still, this is acceptable as it will be
run every day in the morning.. when people are reading emails and
getting coffee etc. Im fine with it Thank you everyone for your help.

BTW Im quite intrigued by using the OR statement. Havent given it a
try, but Im guessing that the OR statement will cause ties within the
first 12 to also shift out, which is unfortunately not acceptable.
Again havent tried it though so I may be wrong. I gotta start adding
primary keys to my tables

PMFBI

I think the Rank "tie-breaking" would involve
you choosing at least one other field and
adding an OR stmt to your WHERE clause,
for example...

(SELECT COUNT(*)
FROM [InsFT] T1
WHERE
(
(T1.Bus = T.Bus)
AND
(
(T1.EDDate > T.EDDate)
OR
(T1.EDDate = T.EDDate
AND
T1.NextID > T.NextID)
)
)
) + 1 AS Rank

That will arbitrarily break a tie if
the records that are tied have different
values for NextID. You know your data.
If NextID won't do it, then please follow
John's advice.

Apologies again for butting in
(especially if I misunderstood).

Ok, I have now translated it for you --

SELECT T.Bus, T.EDDate, T.NextID, (SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate) AS Rank
FROM InsFT AS T
WHERE ((((SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate))<=12))
ORDER BY T.Bus, T.EDDate DESC;

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12 values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S

 

[S Davis]

As a follow up, I never did manage to find a way to break ties for only
the 12th/13th results. I'm sure its possible, but as the incoming data
to the table is from an Excel sheet (linked), I found it much easier to
just add .001, .002, etc onto the different columns containing dates.
When formatted properly they appear to be equal, which is what I
require, but the underlying data is in fact about 3 minutes different.
I was also able to order my ties in this manner.

Easy enough solution, but almost feels like its cheating

PMFBI

I think the Rank "tie-breaking" would involve
you choosing at least one other field and
adding an OR stmt to your WHERE clause,
for example...

(SELECT COUNT(*)
FROM [InsFT] T1
WHERE
(
(T1.Bus = T.Bus)
AND
(
(T1.EDDate > T.EDDate)
OR
(T1.EDDate = T.EDDate
AND
T1.NextID > T.NextID)
)
)
) + 1 AS Rank

That will arbitrarily break a tie if
the records that are tied have different
values for NextID. You know your data.
If NextID won't do it, then please follow
John's advice.

Apologies again for butting in
(especially if I misunderstood).

Ok, I have now translated it for you --

SELECT T.Bus, T.EDDate, T.NextID, (SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate) AS Rank
FROM InsFT AS T
WHERE ((((SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate))<=12))
ORDER BY T.Bus, T.EDDate DESC;

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12 values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S

 

[David F Cox]

Nice idea. There are a lot of variations of that that could be used in a
query. Today I have learned something. thanks.

As a follow up, I never did manage to find a way to break ties for only
the 12th/13th results. I'm sure its possible, but as the incoming data
to the table is from an Excel sheet (linked), I found it much easier to
just add .001, .002, etc onto the different columns containing dates.
When formatted properly they appear to be equal, which is what I
require, but the underlying data is in fact about 3 minutes different.
I was also able to order my ties in this manner.

Easy enough solution, but almost feels like its cheating

PMFBI

I think the Rank "tie-breaking" would involve
you choosing at least one other field and
adding an OR stmt to your WHERE clause,
for example...

(SELECT COUNT(*)
FROM [InsFT] T1
WHERE
(
(T1.Bus = T.Bus)
AND
(
(T1.EDDate > T.EDDate)
OR
(T1.EDDate = T.EDDate
AND
T1.NextID > T.NextID)
)
)
) + 1 AS Rank

That will arbitrarily break a tie if
the records that are tied have different
values for NextID. You know your data.
If NextID won't do it, then please follow
John's advice.

Apologies again for butting in
(especially if I misunderstood).

Ok, I have now translated it for you --

SELECT T.Bus, T.EDDate, T.NextID, (SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate) AS Rank
FROM InsFT AS T
WHERE ((((SELECT COUNT(*)
FROM [InsFT] T1
WHERE T1.Bus = T.Bus
AND T1.EDDate >= T.EDDate))<=12))
ORDER BY T.Bus, T.EDDate DESC;


:

Hi folks,

I received some excellent help on friday with a Top 12 query I am
working with. It works great except that I am having trouble creating
criteria to force the results to return 12 values even if the last
results are tied.

Can anyone help me out?

Sample Data:

Bus....EDDate...NextID
2101..1/1/2003..CVIP
2101..1/2/2003..RR
2101..1/3/2003..TT
2102..1/1/2003..RR
2102..1/2/2003..RR
2102..1/3/2003..TT

So, my table is called InsFT
Labels are Bus, EDDate, and NextID

Can someone help me put this information into the example below?

This is an example tiebreaker (or query that will only return 12
values
and not 13 if the last two values are a tie) written in that post, but
I can't quite follow it in order to translate it over to my query
names
and such. I didnt provide column labels, definitely would have made my
life easier!

SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

I really appreciate it. Sorry for this desperate plea for a
handholding, but I can't get my head around it!

Thanks a lot!
-S