GROUP BY or what else?

Hi All,
I have a set very complicated tables and queries. This is a significantly
simplified schema and one of the subqueries should do the following: This is
an example of the table.
Table tblResponses
---
RespID, PK, int, NOT NULL
UserID int, not null
QiestionID,
Response, varchar(255), not null
RespDate datetime, not null, def=getdate()
The schema is done so that any user can answer same question from 0 to a few
times per day. I need to get only the latest response existing on the
required date using some simple trick. The response can be given on the
@.RequestDate or earlier, it doesn't matter, it should exist on this date.
The basic query is looking like this:
SELECT *
FROM tblResponses
WHERE UserID=@.UserID AND
DATEDIFF(DAY,@.RequestDate,RespDate)<=0
It returns all responses on the date or earlier. How can I get the latest
response only? To use GROUP BY in some subquery doesn't work or I'm doing
something wrong. For example I can write:
SELECT RespDate, Count(RespDate)
FROM tblResponses
WHERE UserID=@.UserID AND
DATEDIFF(DAY,@.RequestDate,RespDate)<=0
GROUP BY RespDate DESC
and get two columns of values - datetimes and number of responses. It will
not work because datetime is not the date and includes the time as well.
Maybe it's easier to group by QuestionID like:
SELECT RespDate, Count(RespDate)
FROM tblResponses
WHERE UserID=@.UserID AND
DATEDIFF(DAY,@.RequestDate,RespDate)<=0
GROUP BY RespDate DESC
Maybe this is the wrong way to go. But what are we having to get the latest
responses from each subgroup assuming that the greater RespID belongs to the
greater response.
Thanks,
Just D."Just D." <no@.spam.please> wrote in message
news:yqOef.93$4v.10@.fed1read03...
> Hi All,
> I have a set very complicated tables and queries. This is a significantly
> simplified schema and one of the subqueries should do the following: This
> is an example of the table.
>
> Table tblResponses
> ---
> RespID, PK, int, NOT NULL
> UserID int, not null
> QiestionID,
> Response, varchar(255), not null
> RespDate datetime, not null, def=getdate()
>
> The schema is done so that any user can answer same question from 0 to a
> few times per day. I need to get only the latest response existing on the
> required date using some simple trick. The response can be given on the
> @.RequestDate or earlier, it doesn't matter, it should exist on this date.
> The basic query is looking like this:
> SELECT *
> FROM tblResponses
> WHERE UserID=@.UserID AND
> DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> It returns all responses on the date or earlier. How can I get the latest
> response only? To use GROUP BY in some subquery doesn't work or I'm doing
> something wrong. For example I can write:
> SELECT RespDate, Count(RespDate)
> FROM tblResponses
> WHERE UserID=@.UserID AND
> DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> GROUP BY RespDate DESC
> and get two columns of values - datetimes and number of responses. It will
> not work because datetime is not the date and includes the time as well.
> Maybe it's easier to group by QuestionID like:
> SELECT RespDate, Count(RespDate)
> FROM tblResponses
> WHERE UserID=@.UserID AND
> DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> GROUP BY RespDate DESC
> Maybe this is the wrong way to go. But what are we having to get the
> latest responses from each subgroup assuming that the greater RespID
> belongs to the greater response.
> Thanks,
> Just D.
>
The best way to specify your problem is to post DDL, sample data and show
your required end result. In the absence of any of those here's an untested
guess:
SELECT respid, userid, questionid, response, respdate
FROM tblResponses AS T
WHERE respdate =
(SELECT MAX(respdate)
FROM tblResponses
WHERE respdate >= @.respdate
AND respdate < DATEADD(DAY,1,@.respdate)
AND userid = @.userid)
AND userid = @.userid ;
Notice that it's generally much more efficient to specify a range for
DATETIMEs rather than try to do date math with the column.
David Portas
SQL Server MVP
--|||Hi David,
You wrote me:

> SELECT respid, userid, questionid, response, respdate
> FROM tblResponses AS T
> WHERE respdate =
> (SELECT MAX(respdate)
> FROM tblResponses
> WHERE respdate >= @.respdate
> AND respdate < DATEADD(DAY,1,@.respdate)
> AND userid = @.userid)
> AND userid = @.userid ;
> Notice that it's generally much more efficient to specify a range for
> DATETIMEs rather than try to do date math with the column.
It returns only one response from the whole table, not the one from each
group if we group by QID that was required, sorry.
Thanks,
Just D.|||Just D,
If you only want the latest response, regardless of QuestionID, then you
are pretty close with your first query: Just add a Top 1 and an order by
clause such as the following:
SELECT top 1 *
FROM tblResponses
WHERE UserID=@.UserID AND DATEDIFF(DAY,@.RequestDate,RespDate)<=0
order by RespDate desc
Hope this helps,
Mark|||Mark,
Thank you for your help. Like the previous answer the query with this
modification returns only one response from the whole bunch of responses.
Maybe the original question wasn't clear.
If we have for example the table:
RespID, PK, int, NOT NULL
UserID int, not null
QiestionID,
Response, varchar(255), not null
RespDate datetime, not null, def=getdate()
and the data in the table is looking like this:
RespID UserID QuestionID Response RespDate
1 1032 1050 'SomeResponse'
'11/16/2005 10:15:12'
2 1032 1052 'SomeResponse'
'11/16/2005 10:15:14'
3 1032 1052 'SomeResponse'
'11/16/2005 10:15:16'
4 1032 1052 'SomeResponse'
'11/16/2005 10:15:19'
5 1032 1051 'SomeResponse'
'11/16/2005 10:15:27'
6 1032 1051 'SomeResponse'
'11/16/2005 10:16:20'
7 1032 1051 'SomeResponse'
'11/16/2005 10:18:20'
8 1032 1050 'SomeResponse'
'11/16/2005 10:28:30'
9 1032 1050 'SomeResponse'
'11/16/2005 10:34:50'
The problem is first - to group by QID, second - find the latest response
for the group (belonging to one QID), return these responses - all
responses, one for each group, in the case above we need to get 3 responses
for QID = 1050, 1051 and 1052 so that the response is the latest for this
group.
SELECT Top 1 *
returns only one response for sure.
Just D.
"mark sullivan" <marksullivan@.rcn.com> wrote in message
news:%23F%23%230hw6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Just D,
> If you only want the latest response, regardless of QuestionID, then you
> are pretty close with your first query: Just add a Top 1 and an order by
> clause such as the following:
> SELECT top 1 *
> FROM tblResponses
> WHERE UserID=@.UserID AND DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> order by RespDate desc
> Hope this helps,
> Mark
>|||Btw,
I tried this:
SELECT * FROM tblResponses
WHERE RespID IN(
SELECT RespID
FROM tblResponses
WHERE UserID=@.UserID AND DATEDIFF(DAY,@.RespDate,RespDate)<=0
GROUP BY RespID
HAVING RespID=Max(RespID)
)
ORDER BY QID,RespID
and this query returns all responses from the whole table, not only the
latest from each group. That's correct because I used
GROUP BY RespID
but should use
GROUP BY QID
The language didn't allow me to group by QID, aggregate, etc.
Just D.|||Poor specs lead to poor results. Please post DDL, sample data and a
description of expected results.
ML|||The thread is closed, thanks to everybody! The final query is:
SELECT * FROM tblResponses
WHERE RespID IN(
SELECT Max(RespID)
FROM tblResponses
WHERE PtID=@.PtID AND DATEDIFF(DAY,@.RespDate,RespDate)<=0
GROUP BY QID
)
ORDER BY QID,RespID
Just D.|||FIRST, learn netiquette: Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are.
In order to do your job for you, we have to guess and decode what you
meant in that personal and very rude pseudo-code you posted.
Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it.
SECOND: Never use "tbl-" prefixes on table names. This shows us that
you are not and SQL programmer.
THIRD: Why isn't (user_id, question_nbr) the natural key? Why did you
need to construct a "response_id" ; I need a LOGICAL reason, not some
crap about using an IDENTITY column.
FOURTH: the Standard SQL syntax is CURRENT-TIMESTAMP, not getdate().
FIFTH: the GROUP BY clause does not have an ordering option. This
reallllllly tells us you do not write SQL. That is soooo fundamental
and it would have crashed if you ran the code.
SELECT R1.resp_date, COUNT(*)
FROM Responses AS R1
WHERE user_id = @.my_user_id
AND resp_date
= (SELECT MAX(resp_date)
FROM Responses AS R2
WHERE R1.user_id = R2.user_id
AND R1.user_id = R2.user_id);
But you have more problems than this and need to get someone more
experienced to help you with your SQL.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132197969.434508.50880@.g44g2000cwa.googlegroups.com...
> FOURTH: the Standard SQL syntax is CURRENT-TIMESTAMP, not
getdate().
Ahem . . . that's CURRENT_TIMESTAMP.
Sincerely,
Chris O.