SQL: Select the row with the latest time in groups

[fix]

Hi all,

I am writing a voting system for a committee election.

Here is the table structure:
user (the one who voted)
position
candidate_id
time (time of vote)

Each row records the user name of the one who voted, which candidate for
which position and the time of vote. Everyone has one vote for each
position, but they can vote again if they can their mind - only the last
vote counts.

But to select the last votes per user per position has gotten me into
trouble.
I can do
SELECT [user], [position], MAX([time]) FROM votes GROUP BY [user],
[position]
to select the time of the latest votes, but I cannot get candidate_id of
that vote into the SELECT clause, I'll get an error:
SELECT [user], [position], MAX([time]), candidate_id FROM votes GROUP BY
[user], [position]

So what should I do to get the whole row with the latest time per user per
position.

Thanks.
fix.

--

 

[[MVP] S.Clark]

Do it in two queries. First to get the latest time, the second links to the
first to get to the CandidateID.

 

[Graham R Seach]

SELECT B.MaxTime, A.[user], A.[candidate_id], A.[position]
FROM votes As A
INNER JOIN
(SELECT Max([time]) As MaxTime, [user], [candidate_id]
FROM votes
GROUP BY [user], [candidate_id]) As B
ON B.MaxTime = A.[time]
AND B.[user] = A.[user]
AND B.[candidate_id] = A.[candidate_id]

By the way, [time], [position] and [user] are all Access reserved keywords.
I strongly advise renaming these fields.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[fix]

Sorry maybe I wasn't too clear. Actually there is only one candidate list,
so every candidate appears in every position available. With your query, I
got the lastest vote for each candidate by the voters.
About the reserved words. Is there a list that I can refer to, so I won't
use them later? Actually I couldn't think of anything more descriptive than
"user" and "time" (maybe "date", but I guess it's still a reserved word).

--

SELECT B.MaxTime, A.[user], A.[candidate_id], A.[position]
FROM votes As A
INNER JOIN
(SELECT Max([time]) As MaxTime, [user], [candidate_id]
FROM votes
GROUP BY [user], [candidate_id]) As B
ON B.MaxTime = A.[time]
AND B.[user] = A.[user]
AND B.[candidate_id] = A.[candidate_id]

By the way, [time], [position] and [user] are all Access reserved keywords.
I strongly advise renaming these fields.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi all,

I am writing a voting system for a committee election.

Here is the table structure:
user (the one who voted)
position
candidate_id
time (time of vote)

Each row records the user name of the one who voted, which candidate for
which position and the time of vote. Everyone has one vote for each
position, but they can vote again if they can their mind - only the last
vote counts.

But to select the last votes per user per position has gotten me into
trouble.
I can do
SELECT [user], [position], MAX([time]) FROM votes GROUP BY [user],
[position]
to select the time of the latest votes, but I cannot get candidate_id of
that vote into the SELECT clause, I'll get an error:
SELECT [user], [position], MAX([time]), candidate_id FROM votes GROUP BY
[user], [position]

So what should I do to get the whole row with the latest time per user per
position.

Thanks.
fix.

 

[fix]

Do I have to do the second query for each latest time?
The front-end is actually by ASP, so if I have to do multiple second query,
maybe it could take a while to give feedback.

--

Do it in two queries. First to get the latest time, the second links to the
first to get to the CandidateID.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi all,

I am writing a voting system for a committee election.

Here is the table structure:
user (the one who voted)
position
candidate_id
time (time of vote)

Each row records the user name of the one who voted, which candidate for
which position and the time of vote. Everyone has one vote for each
position, but they can vote again if they can their mind - only the last
vote counts.

But to select the last votes per user per position has gotten me into
trouble.
I can do
SELECT [user], [position], MAX([time]) FROM votes GROUP BY [user],
[position]
to select the time of the latest votes, but I cannot get candidate_id of
that vote into the SELECT clause, I'll get an error:
SELECT [user], [position], MAX([time]), candidate_id FROM votes GROUP BY
[user], [position]

So what should I do to get the whole row with the latest time per user per
position.

Thanks.
fix.

 

[Graham R Seach]

That's what I thought you wanted. Oh well.

SELECT B.MaxTime, A.[user], A.[candidate_id], A.[position]
FROM votes As A
INNER JOIN
(SELECT Max([time]) As MaxTime, [user], [position]
FROM votes
GROUP BY [user], [position]) As B
ON B.MaxTime = A.[time]
AND B.[user] = A.[user]
AND B.[position] = A.[position]

You can find the keyword list in the online Help.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Sorry maybe I wasn't too clear. Actually there is only one candidate list,
so every candidate appears in every position available. With your query, I
got the lastest vote for each candidate by the voters.
About the reserved words. Is there a list that I can refer to, so I won't
use them later? Actually I couldn't think of anything more descriptive than
"user" and "time" (maybe "date", but I guess it's still a reserved word).

--

SELECT B.MaxTime, A.[user], A.[candidate_id], A.[position]
FROM votes As A
INNER JOIN
(SELECT Max([time]) As MaxTime, [user], [candidate_id]
FROM votes
GROUP BY [user], [candidate_id]) As B
ON B.MaxTime = A.[time]
AND B.[user] = A.[user]
AND B.[candidate_id] = A.[candidate_id]

By the way, [time], [position] and [user] are all Access reserved keywords.
I strongly advise renaming these fields.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi all,

I am writing a voting system for a committee election.

Here is the table structure:
user (the one who voted)
position
candidate_id
time (time of vote)

Each row records the user name of the one who voted, which candidate for
which position and the time of vote. Everyone has one vote for each
position, but they can vote again if they can their mind - only the last
vote counts.

But to select the last votes per user per position has gotten me into
trouble.
I can do
SELECT [user], [position], MAX([time]) FROM votes GROUP BY [user],
[position]
to select the time of the latest votes, but I cannot get candidate_id of
that vote into the SELECT clause, I'll get an error:
SELECT [user], [position], MAX([time]), candidate_id FROM votes GROUP BY
[user], [position]

So what should I do to get the whole row with the latest time per user per
position.

Thanks.
fix.

 

[fix]

Oh yay, I got it.
Thanks very very much!
It is just pretty hard to think in SQL than in a traditional programming
language.

--

That's what I thought you wanted. Oh well.

SELECT B.MaxTime, A.[user], A.[candidate_id], A.[position]
FROM votes As A
INNER JOIN
(SELECT Max([time]) As MaxTime, [user], [position]
FROM votes
GROUP BY [user], [position]) As B
ON B.MaxTime = A.[time]
AND B.[user] = A.[user]
AND B.[position] = A.[position]

You can find the keyword list in the online Help.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Sorry maybe I wasn't too clear. Actually there is only one candidate list,
so every candidate appears in every position available. With your query, I
got the lastest vote for each candidate by the voters.
About the reserved words. Is there a list that I can refer to, so I won't
use them later? Actually I couldn't think of anything more descriptive than
"user" and "time" (maybe "date", but I guess it's still a reserved word).

--

SELECT B.MaxTime, A.[user], A.[candidate_id], A.[position]
FROM votes As A
INNER JOIN
(SELECT Max([time]) As MaxTime, [user], [candidate_id]
FROM votes
GROUP BY [user], [candidate_id]) As B
ON B.MaxTime = A.[time]
AND B.[user] = A.[user]
AND B.[candidate_id] = A.[candidate_id]

By the way, [time], [position] and [user] are all Access reserved keywords.
I strongly advise renaming these fields.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Hi all,

I am writing a voting system for a committee election.

Here is the table structure:
user (the one who voted)
position
candidate_id
time (time of vote)

Each row records the user name of the one who voted, which candidate for
which position and the time of vote. Everyone has one vote for each
position, but they can vote again if they can their mind - only the last
vote counts.

But to select the last votes per user per position has gotten me into
trouble.
I can do
SELECT [user], [position], MAX([time]) FROM votes GROUP BY [user],
[position]
to select the time of the latest votes, but I cannot get

candidate_id

of

that vote into the SELECT clause, I'll get an error:
SELECT [user], [position], MAX([time]), candidate_id FROM votes

GROUP

BY

[user], [position]

So what should I do to get the whole row with the latest time per

user
per

position.

Thanks.
fix.