Need SQL Help

[WannaFly]

table structure:
id numeric - PK
EmplID - nvarcar
plate - nvarchar
date - timestamp


Rows are only ever inserted, never modified or deleted. This is basicallly a lookup table. I want to fine what plates belong to certian emplids and what emplids belong to plates. When an association is broken, emplid is set to null.

I need a query that will tell me what plates an emplid owns currently. Seems easy. Ex:

emplid plate
123 qrs
123 abc
456 def
null abc
123 xyz

the query i need would return qrs and xyz but NOT abc because an entry exists with a null with a later timestamp. Instead of null it could be anything else too (if it was 456 instead of null, the query shouldnt return it)

kind of a longconfusing situation, but it seems like something so easy - either my brain isnt working or I just cant figure this one out. Let me know if i havent been clear enough, thanks.

 

[oog]

select top 1 emplid, plate from tablename where emplid = @input_emplid order by [date]

i don't remember if date should be ascending or descending to get what you want. essentially you ask for the first row that matches the emplid when the results are sorted by date. nulls won't match.

edit: sorry, top 1 won't work. i didn't read your post closely enough. i thought you were asking for the most recent one. off the top of my head, i would use a subquery to find the most recent date and match that in the where clause.

 

[shutterFly]

Try:

SELECT emplid, plate, MAX(timestamp)
FROM table
GROUP BY emplid, plate

 

[WannaFly]

oog: Im not sure how the subquery would work because the most recent date is differnet on every plate

shutterFly: that doesnt account for ones that have null associates, it just returns all that were ever associated with that emplid.
ie from my data above it would return qrs,abc,xyz even though abc should not be returned.

Thanks for the input though. Im still working on figuring it out, have no clue hah

 

[shutterFly]

Originally posted by: WannaFly
oog: Im not sure how the subquery would work because the most recent date is differnet on every plate

shutterFly: that doesnt account for ones that have null associates, it just returns all that were ever associated with that emplid.
ie from my data above it would return qrs,abc,xyz even though abc should not be returned.

Thanks for the input though. Im still working on figuring it out, have no clue hah

Haha! You're right... brain fart on my part...

Anyway, this might do what you need:

SELECT emplid, plate, timestamp
FROM table t
WHERE t.timestamp =
(
SELECT MAX(timestamp)
FROM table
WHERE plate = t.plate
)

 

[WannaFly]

Exactly what I needed, thanks ShutterFly! It was one of those things where I went "doh! it was that easy " .