MS SQL Server MSEQ - Re: filtering groups

Author

Re: filtering groups

zoe1982

2005-06-01, 8:24 pm

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
OperatorLicenseNumbe
r) and the violations table (violatorID(foreign
key), DateTimeViolation)... I want to be able to return the last record
of a datetimeviolation, and the operatorlicensenumbe
r of that last
violation. How would I go about putting it into code? So far, I've
got:

SELECT a.ViolatorID, OperatorLicenseNumbe
r, 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 operatorlicensenumbe
r?

Ugh... I love SQL, but help!

--
zoe1982
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message948760.html

Hugo Kornelis

2005-06-02, 8:24 pm

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
> OperatorLicenseNumbe
r) and the violations table (violatorID(foreign
>key), DateTimeViolation)... I want to be able to return the last record
>of a datetimeviolation, and the operatorlicensenumbe
r of that last
>violation. How would I go about putting it into code? So far, I've
>got:
>
>SELECT a.ViolatorID, OperatorLicenseNumbe
r, 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 operatorlicensenumbe
r?

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. OperatorLicenseNumbe
r, 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)

Sponsored Links