CODEWITHSUNDEEP
sqlms-access-2010
Gary Cheary
Gary Cheary

Reputation: 1

Using SQL Not sure how to do iterative SQL

I am analysing some running club data and trying to see who has the highest Personal Best (PB) sequence.

I have the data stored in an Access database and I am accessing using SQL

I can establish when an athlete has achieved a PB when the field "Note" is set to "New PB!"

So in the data below I have sorted on event number descending

Data from Athlete table

select * from athlete
where number in ( 2033654, 4120642, 2924648, 6797408, 5544924)
order by number, event desc

Number  Name    Time    Event   Note

2033654 Helen BLACKBURN 00:28:50    113 New PB!

2033654 Helen BLACKBURN 00:29:56    112 New PB!

2033654 Helen BLACKBURN 00:30:19    111 PB 30:03

2033654 Helen BLACKBURN 00:30:03    51  New PB!

2033654 Helen BLACKBURN 00:31:44    47  First Timer!

2924648 James FORDE 00:27:07    113 New PB!

2924648 James FORDE 00:28:57    112 PB 27:12

2924648 James FORDE 00:30:45    111 PB 27:12

2924648 James FORDE 00:31:28    104 PB 27:12

2924648 James FORDE 00:29:14    102 PB 27:12

2924648 James FORDE 00:27:12    96  New PB!

2924648 James FORDE 00:29:16    95  First Timer!

4120642 Tim WARD    00:29:16    113 New PB!

4120642 Tim WARD    00:29:31    112 First Timer!

5544924 James LITTLE    00:25:56    113 New PB!

5544924 James LITTLE    00:27:12    35  New PB!

5544924 James LITTLE    00:28:12    34  New PB!

5544924 James LITTLE    00:29:26    33  New PB!

5544924 James LITTLE    00:31:01    31  First Timer!

6797408 Tom GREENWOOD   00:23:24    113 New PB!

6797408 Tom GREENWOOD   00:24:47    112 New PB!

6797408 Tom GREENWOOD   00:26:36    110 New PB!

6797408 Tom GREENWOOD   00:27:12    108 New PB!

6797408 Tom GREENWOOD   00:29:05    107 New PB!

6797408 Tom GREENWOOD   00:35:43    106 First Timer!

The SQL I am looking for would report the following

Name  PB Sequence

Tom Greenwood 5

James Little  4

Helen Blackburn  2 

James Forde has  1

Tim Ward has a  1

I have lots of SQL experience but I am really not sure where to start on this.

I feel I need some kind of iterative SQL statement, but I have not done this kind of query before.

Upvotes: 0

Views: 37

Answers (2)

Gustav
Gustav

Reputation: 55921

You can use a simple subquery:

SELECT 
    Athlete.[Name], 
    Count(*) AS Sequence
FROM 
    Athlete 
INNER JOIN 
    (SELECT Athlete.Number, Max(Athlete.Event) AS StartEvent
    FROM Athlete
    WHERE Athlete.Note <> "New PB!"
    GROUP BY Athlete.Number) AS T 
    ON Athlete.Number = T.Number
WHERE 
    Athlete.Event > [StartEvent]
GROUP BY 
    Athlete.[Name];

enter image description here

Upvotes: 1

user18098820
user18098820

Reputation:

The following should return the result looked for.
There will be trailing empty space in the PB column but it will be invisible. The alphabetic sort will return the same value as a numerical sort in this case.

select 
  number, 
  name, 
  max(left(Note,4)) PB
from athlete
where number in ( 2033654, 4120642, 2924648, 6797408, 5544924)
group by number, name
order by number, name;

Upvotes: 0

Related Questions