BRAIN TEASER - Create query (or series of queries) to rank several items from a table

[thexxvi]

Good Afternoon,

I have a brain teaser here. I am attempting to create a query (or series of queries) to rank records in a table. Below is an example of the table:


Standings

ID	Team Name	Wins	Losses	Ties	Points For	Points Against	Division
1	Team 1	1	0	0	167	133	East
2	Team 2	1	0	0	144	123	East
3	Team 3	1	0	0	133	114	East
4	Team 4	1	0	0	131	122	East
5	Team 5	1	0	0	110	101	East
6	Team 6	0	1	0	133	167	East
7	Team 7	0	1	0	123	144	East
8	Team 8	0	1	0	122	131	East
9	Team 9	0	1	0	114	133	East
10	Team 10	0	1	0	101	110	East
11	Team 11	1	0	0	142	118	West
12	Team 12	1	0	0	141	122	West
13	Team 13	1	0	0	138	115	West
14	Team 14	1	0	0	121	114	West
15	Team 15	1	0	0	110	103	West
16	Team 16	0	1	0	122	141	West
17	Team 17	0	1	0	118	142	West
18	Team 18	0	1	0	115	138	West
19	Team 19	0	1	0	114	121	West
20	Team 20	0	1	0	103	110	West
21	Team 21	1	0	0	165	100	Central
22	Team 22	1	0	0	148	147	Central
23	Team 23	1	0	0	139	106	Central
24	Team 24	1	0	0	130	82	Central
25	Team 25	1	0	0	113	112	Central
26	Team 26	0	1	0	147	148	Central
27	Team 27	0	1	0	112	113	Central
28	Team 28	0	1	0	106	139	Central
29	Team 29	0	1	0	100	165	Central
30	Team 30	0	1	0	82	130	Central

To establish seeding for playoffs, the rules that I have to follow are the following:

All rankings are based on number of wins first, then points for.
The top team from each division gets the top 3 seeds (ranked based on wins, then points for) - these teams will make up seeds 1, 2, and 3
The second team from each division gets the next 3 seeds (ranked based on wins, then points for) - these teams will make up seeds 4, 5, and 6
The rest of the teams, regardless of division, are eligible for wildcard spots (ranked based on wins, then points for) - these teams will make up seeds 7, 8, 9, 10, 11, and 12


Seeds 13 - 30 will not be eligible for the playoffs.

I understand that this could take a few queries to get what I want. I actually created queries for each of the divisions to rank everyone within their respective divisions thinking that would be a good place to start, but I really don't know how to go from there.

Any help would be greatly appreciated.

Thank you.

Cory

[thexxvi]

This is essentially what I'm looking for. Based on the rules and the data above, I should get:

ID	Team Name	Wins	Losses	Ties	Points For	Points Against	Division	Playoff Seeding
1	Team 1	1	0	0	167	133	East	1
21	Team 21	1	0	0	165	100	Central	2
11	Team 11	1	0	0	142	118	West	3
22	Team 22	1	0	0	148	147	Central	4
2	Team 2	1	0	0	144	123	East	5
12	Team 12	1	0	0	141	122	West	6
23	Team 23	1	0	0	139	106	Central	7
13	Team 13	1	0	0	138	115	West	8
3	Team 3	1	0	0	133	114	East	9
4	Team 4	1	0	0	131	122	East	10
24	Team 24	1	0	0	130	82	Central	11
14	Team 14	1	0	0	121	114	West	12
25	Team 25	1	0	0	113	112	Central	-
5	Team 5	1	0	0	110	101	East	-
15	Team 15	1	0	0	110	103	West	-
26	Team 26	0	1	0	147	148	Central	-
6	Team 6	0	1	0	133	167	East	-
7	Team 7	0	1	0	123	144	East	-
8	Team 8	0	1	0	122	131	East	-
16	Team 16	0	1	0	122	141	West	-
17	Team 17	0	1	0	118	142	West	-
18	Team 18	0	1	0	115	138	West	-
19	Team 19	0	1	0	114	121	West	-
9	Team 9	0	1	0	114	133	East	-
27	Team 27	0	1	0	112	113	Central	-
28	Team 28	0	1	0	106	139	Central	-
20	Team 20	0	1	0	103	110	West	-
10	Team 10	0	1	0	101	110	East	-
29	Team 29	0	1	0	100	165	Central	-
30	Team 30	0	1	0	82	130	Central	-

[orange]

A series of queries (close??)
QSeedings1

SELECT t1.teamname , t1.pointsfor , t1.division , t1.id
FROM seedings AS t1
WHERE (((t1.id) In (SELECT   T2.Id FROM seedings As T2
 WHERE T2.teamname = T1.teamname ORDER BY T2.ID asC))) and t1.wins >0
GROUP BY t1.teamname , t1.pointsfor,t1.division,t1.id
ORDER BY  t1.pointsfor DESC,t1.division DESC , t1.teamname;

QSeedings2

SELECT TOP 1 
 QSeedings1.id, QSeedings1.division, QSeedings1.pointsfor,1 as seq
FROM QSeedings1
where division =  "East"
union
SELECT TOP 1
 QSeedings1.id, QSeedings1.division, QSeedings1.pointsfor,2 as seq
FROM QSeedings1
where division ="Central"
union
SELECT TOP 1
 QSeedings1.id, QSeedings1.division, QSeedings1.pointsfor,3 as seq
FROM QSeedings1
where division = "West"
Order by QSeedings1.pointsfor desc
union
SELECT TOP 1 
 QSeedings1.id, QSeedings1.division, QSeedings1.pointsfor,4 as seq
FROM QSeedings1
where division = "Central" and QSeedings1.id Not in (SELECT TOP 1 
 QSeedings1.id 
FROM QSeedings1
where division = "Central")
union
SELECT TOP 1 
 QSeedings1.id, QSeedings1.division, QSeedings1.pointsfor,5 as seq
FROM QSeedings1
where division = "East" and QSeedings1.id Not in (SELECT TOP 1 
 QSeedings1.id 
FROM QSeedings1
where division = "East")
UNION SELECT TOP 1 
 QSeedings1.id, QSeedings1.division, QSeedings1.pointsfor,6 as seq
FROM QSeedings1
where division = "west" and QSeedings1.id Not in (SELECT TOP 1 
 QSeedings1.id 
FROM QSeedings1
where division = "west")
ORDER BY seq;

QSeedings3

SELECT TOP 6 QSeedings1.pointsfor, QSeedings1.division, QSeedings1.id
FROM QSeedings1
WHERE (((QSeedings1.id) Not In (select id from qseedings2)));

Qseedings5

 SELECT id,Division,Pointsfor,
           (SELECT 13 - COUNT(T1.PointsFor)
              FROM
                     [qseedings3] AS T1
             WHERE T1.Pointsfor <= T2.pointsfor ) AS seq
    FROM
          [qseedings3] AS T2
    ORDER BY pointsfor DESC

QSeedings4 <---this is final answer/attempt

select * from qseedings2
union select * from qseedings5
order by seq

Result of Qseedinds1

teamname
pointsfor
division
id

Team 1
167
East
1

Team 21
165
Central
21

Team 22
148
Central
22

Team 2
144
East
2

Team 11
142
West
11

Team 12
141
West
12

Team 23
139
Central
23

Team 13
138
West
13

Team 3
133
East
3

Team 4
131
East
4

Team 24
130
Central
24

Team 14
121
West
14

Team 25
113
Central
25

Team 15
110
West
15

Team 5
110
East
5

Result of qseedings2

id
division
pointsfor
seq

1
East
167
1

21
Central
165
2

11
West
142
3

22
Central
148
4

2
East
144
5

12
West
141
6

Result of Qseedings3

pointsfor
division
id

139
Central
23

138
West
13

133
East
3

131
East
4

130
Central
24

121
West
14

Result of QSeedings5

Pointsfor
Division
id
seq

139
Central
23
7

138
West
13
8

133
East
3
9

131
East
4
10

130
Central
24
11

121
West
14
12

Result of QSeedings4

id
division
pointsfor
seq

1
East
167
1

21
Central
165
2

11
West
142
3

22
Central
148
4

2
East
144
5

12
West
141
6

23
Central
139
7

13
West
138
8

3
East
133
9

4
East
131
10

24
Central
130
11

14
West
121
12

[thexxvi]

Thank you orange! I'll give this a shot. I knew it had to be a few queries.

[thexxvi]

Orange, I'm having a lot of trouble with this, and I think it's because of the table name. I have one table with the data called Standings. I'm confused what the two tables are in QSeedings1.

[thexxvi]

Any thoughts?

[orange]

I took the data from your post

I called it Seedings, not Standings.

Here is the table Seedings.

ID	TeamName	Wins	Losses	Ties	PointsFor	PointsAgainst	Division
1	Team 1	1	0	0	167	133	East
2	Team 2	1	0	0	144	123	East
3	Team 3	1	0	0	133	114	East
4	Team 4	1	0	0	131	122	East
5	Team 5	1	0	0	110	101	East
6	Team 6	0	1	0	133	167	East
7	Team 7	0	1	0	123	144	East
8	Team 8	0	1	0	122	131	East
9	Team 9	0	1	0	114	133	East
10	Team 10	0	1	0	101	110	East
11	Team 11	1	0	0	142	118	West
12	Team 12	1	0	0	141	122	West
13	Team 13	1	0	0	138	115	West
14	Team 14	1	0	0	121	114	West
15	Team 15	1	0	0	110	103	West
16	Team 16	0	1	0	122	141	West
17	Team 17	0	1	0	118	142	West
18	Team 18	0	1	0	115	138	West
19	Team 19	0	1	0	114	121	West
20	Team 20	0	1	0	103	110	West
21	Team 21	1	0	0	165	100	Central
22	Team 22	1	0	0	148	147	Central
23	Team 23	1	0	0	139	106	Central
24	Team 24	1	0	0	130	82	Central
25	Team 25	1	0	0	113	112	Central
26	Team 26	0	1	0	147	148	Central
27	Team 27	0	1	0	112	113	Central
28	Team 28	0	1	0	106	139	Central
29	Team 29	0	1	0	100	165	Central
30	Team 30	0	1	0	82	130	Central