query help

[wengang]

Given a table like this:

tblScores

ID Name Date Score
1 Adam 8/9/11 100
2 Bill 8/9/11 95
3 Charles 8/9/11 98
4 Adam 8/8/11 100
5 Bill 8/8/11 88
6 Charles 8/8/11 93
7 Adam 8/7/11 100
8 Bill 8/7/11 89
9 Charles 8/7/11 97

This is just an example of course.
I am trying to think of a query that will tell me the name of every person who has had a score of 100 for the past 3 days in a row (i.e. the most recent 3 days)

In this example
select name from tblScores where {most recent three records in this table have score = 100}

and in this example, the query would return "Adam"


Anybody?

[techgnome]

Access? SQL Server? MySQL? helps to know what type of database we're dealing with here.

-tg

[seenu_1st]

this link may be useful for u

[dee-u]

Something like

SELECT TOP 3  Table1.DDate , Table1.ID, Table1.SName, Table1.Score
FROM Table1 WHERE Score >= 100 ORDER BY DDAte DESC;

[Ecniv]

Think it would need to be code or script as you are evaluating those >=100 over three days consecutively.

hmmm.. Unless.




If you are only looking at the last three days :

SELECT t.username, count(t.username) as TheTotal
FROM Table as T
WHERE t.CreatedDate>=format(now()-3,"dd mmm yyyy")
AND t.score>=100
GROUP BY t.UserName
HAVING TheTotal=3

That should list only those with 3 in the last three days...


But definately would need coding if you are spanning a larger date range and still looking for those who had three consecutive...

[wengang]

Ecniv,
That's look like exactly what I need.
Actually it isn't the last 3 days, its the last three days for which records exist.
I guess I would just do a select distinct top 3 CreatedDate .... order by CreatedDate Desc
and then take the date from the 3rd record and put that in this query dynamically.
I'm looking forward to trying it out.
Thanks.

[wengang]

it isn't working
I'm getting invalid column name 'TheTotal'
THis is SQL Server 2005

[wengang]

OK
it works if I change your last line to

Having count(t.username) = 3

[wengang]

sorry to pile on, if you happen to know this,
how can I add a column from another table joined on the username

i.e. change:
SELECT t.username, count(t.username) as TheTotal
FROM Table1 as T

to

SELECT t.username, count(t.username) as TheTotal, p.email
FROM Table1 as T left join Table2 as p on T.username = p.username

This won't work because I get that "p.email is an invalid column name becuase it is not part of an aggregate function or included in the GroupBy clause" error

the rest of the syntax is ok. if i take out "p.email" it executes the query, but of course with no new column

[GaryMazzone]

p.email needs be added to a Group By clause.

[FunkyDexter]

Just a quick note of caution, this won't work if there are multiple emails to one user because it'll increase the number of records in the record set and mess up your counts.

[wengang]

Actually I was simplifying the idea.
There is a second table, tblUsers
that contains various user information.
I was hoping in addition to getting those usernames who had the three records like i described above (which is accomplished well with Ecniv's code)
to add a number of columns from the other table to the record set.

So it would be like p.email, p.telephone, p.birthdate etc
In this table person is only listed once, so there is no concern over the same person being pulled more than once. The worry is that the birthdate or other info would identical.

Currently, I'm just doing it in code, getting the first RS back and then looping through it for the other data. But it is sloooooooow.

I guess another way might be to build a temporary table in a stored procedure and just stick the data together as records, then query that table. Sound right? IT seems like I did something like that a few years ago.