Re: filtering groups
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 02 Jun 2005 22:05:24 +0200
On Wed, 1 Jun 2005 09:08:28 -0500, zoe1982 wrote:
>
>Ok, I'm in need of a little help... I know this is a simple task, but I
>need a push since my brain isn't working... I have a database that
>allows officers to give tickets to violators... I'm trying to create a
>query that joins the violators table (violatorID (primary key), and
>OperatorLicenseNumber) and the violations table (violatorID(foreign
>key), DateTimeViolation)... I want to be able to return the last record
>of a datetimeviolation, and the operatorlicensenumber of that last
>violation. How would I go about putting it into code? So far, I've
>got:
>
>SELECT a.ViolatorID, OperatorLicenseNumber, DateTimeViolation
>FROM Violators AS a INNER JOIN Violations AS b
>ON a.ViolatorID = b.ViolatorID
>
>but how do I pull up the last datetimeviolation, along with showing the
>violators operatorlicensenumber?
Hi zoe1982,
I'm not sure if I understand you correctly. The best way to ask for help
in these groups is to include CREATE TABLE and INSERT statements with
some sample data in your post, plus the required output from that sample
data. This is described in more detail at www.aspfaq.com/5006.
However, try if this fits your needs. If not, then please repost with
the extra information indicated above.
SELECT a.ViolatorID, a.OperatorLicenseNumber, b.DateTimeViolation
FROM Violators AS a
INNER JOIN Violations AS b
ON b.ViolatorID = a.ViolatorID
WHERE b.DateTimeViolation =
(SELECT MAX(c.DateTimeViolation)
FROM Violations AS c
WHERE c.ViolatorID = a.ViolatorID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
.
- References:
- Re: filtering groups
- From: zoe1982
- Re: filtering groups
- Prev by Date: Re: Identity or Sequence column in non-table SELECT ?
- Next by Date: Re: Identity or Sequence column in non-table SELECT ?
- Previous by thread: Re: filtering groups
- Next by thread: Wildcard with Update
- Index(es):
Relevant Pages
|
|
