Duplicate entries - 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

General SQL Server Forums

New to SQL Server Programming

Duplicate entries

Author

Topic

Madison
Starting Member

4 Posts

Posted - 2006-03-28 : 08:37:17

I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:

Select
L.URN,
LH.THE_TIMESTAMP,
LH.ACTION_TYPE,
LH.ACTION_BY,
LH.ACTION_REQD,
LH.NOTE,
em.position_type_ref
From
LOG L
Join Log_history LH on (L.URN = LH.LOG_URN)
left outer join employee em on (em.code = LH.action_by)
Where
(L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')

Order By
L.URN ASC, LH.THE_TIMESTAMP DESC

The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-28 : 08:39:53

Are they exact duplicates or is only the timestamp a duplicate? If they are exact, then just add DISTINCT..

Madison
Starting Member

4 Posts

Posted - 2006-03-28 : 08:48:52

Thanks...but where does DISTINCT go

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 08:52:07

Can you post some sample data of the duplicates ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Madison
Starting Member

4 Posts

Posted - 2006-03-28 : 08:58:02

Thank you very much indeed

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-28 : 09:05:04

Look in Books Online, but straight after select..

Madison
Starting Member

4 Posts

Posted - 2006-03-29 : 03:33:49

Thanks for all help so far. However although the rows appear identical as in the following example. The 'seconds' which are not shown in the output (hh:mm:ss) are not and this means the duplicates are not removed. Is there away of shortening the time string that is returned by the query

01/02/2006 19:27 OWLTRIAGE NURSE
01/02/2006 19:27 OWLTRIAGE NURSE

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-29 : 04:27:42

Look up Convert in Books Online, but this will do what you want I think..

select CONVERT(varchar(16),getdate(),131)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources