Query for most recent of duplicate records

I need some ideas on this query.
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:

> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:

> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>

Query every entry not in each hour

Hello,

I have been having a tough time writing the follow requirement for a
query.

On a table that the primary key is a tagId and an hourly timestamp, I
would like to find out for every hour which tags did not get entered
into the database. Essentially I am looking for patterns of entries
that are not making it into tableB.

Examples of the tables:
TableA TableB
TagID and TagName TagId Timestamp
PK PK1 PK2

approx 6000 rows approx 6000 rows per hour

I am thinking that I will need to do something like:

Select tableB1.time, count(*) from tableB1 group by tableB1.time
having tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagId
not in (select tagId from tableA where not exists (select
tableA.tagId, distinct.tableB2.time from tableB2)

I have been trying to create an effecient query handle this but have
not had any luck. Any assistance would be more then appreciated.

Thanks,
AndyThis requires a third tables. It would have a row for every hourly
timestamp, regardless of whether there are rows for that timestamp for
the table you described. You MIGHT get away without that table by
deriving it from TableB, but I suspect that would result in gaps.

The query performs a cross join, then eliminates rows that match
TableB using EXISTS.

SELECT *
FROM TableA as A
CROSS JOIN
TimeStamps as C
WHERE NOT EXISTS
(select * from TableB as B
where A.TagiD = B.TagIC
and C.Timestamp = B.Timestamp)

Roy Harvey
Beacon Falls, CT

On Tue, 17 Jul 2007 17:22:09 -0000, mcdonaghandy@.gmail.com wrote:

Quote:

Originally Posted by

>Hello,
>
>I have been having a tough time writing the follow requirement for a
>query.
>
>On a table that the primary key is a tagId and an hourly timestamp, I
>would like to find out for every hour which tags did not get entered
>into the database. Essentially I am looking for patterns of entries
>that are not making it into tableB.
>
>Examples of the tables:
>TableA TableB
>TagID and TagName TagId Timestamp
>PK PK1 PK2
>
>approx 6000 rows approx 6000 rows per hour
>
>I am thinking that I will need to do something like:
>
>Select tableB1.time, count(*) from tableB1 group by tableB1.time
>having tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagId
>not in (select tagId from tableA where not exists (select
>tableA.tagId, distinct.tableB2.time from tableB2)
>
>I have been trying to create an effecient query handle this but have
>not had any luck. Any assistance would be more then appreciated.
>
>Thanks,
>Andy