select only one record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select only one record

Author

Topic

feelingsheepish
Starting Member

8 Posts

Posted - 2009-09-07 : 09:55:39

Hi,

I have an audit table thats contains dates when an item was audited.
I am currently displaying all items that have an audit date greater than a year old.

However, I would like it so that each item would display a single record where its date(ts) is greater than a year by itemID.

Can anyone help please?

SELECT     ts, ItemID, AuditID
FROM         tblAudit
WHERE     (ts < DATEADD(year, - 1, GETDATE()))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-07 : 09:58:18

SELECT max(ts) as ts, ItemID, AuditID
FROM tblAudit
WHERE (ts < DATEADD(year, - 1, GETDATE()))
group by ItemID, AuditID

Madhivanan

Failing to plan is Planning to fail

feelingsheepish
Starting Member

8 Posts

Posted - 2009-09-07 : 11:08:26

This still returns several records for a single ItemID.

I would like to display only the most recent date for each ItemID

Thanks

feelingsheepish
Starting Member

8 Posts

Posted - 2009-09-07 : 11:22:59

Got this working now:

SELECT     MAX(tblAudit.ts) AS ts, tblAudit.ItemID, tblItem.ItemNo
FROM         tblAudit INNER JOIN
                      tblItem ON tblAudit.ItemID = tblItem.ItemID
WHERE     (tblAudit.ts < DATEADD(year, - 1, GETDATE()))
GROUP BY tblAudit.ItemID, tblItem.ItemNo

Many thanks for help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources