Eliminating duplicates without suing temp table

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

Eliminating duplicates without suing temp table

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-10-29 : 15:36:40

SELECT AUDIT.PKGID,AUDIT.PROCESSID, EVENT.NAME,AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID
AND AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)
ORDER BY AUDIT.AUDITSTAMP DESC;

the out of this query is like this

100 3 attorney 2010-10-29 10:03:57
100 3 agency 2010-10-29 09:49:16
101 5 Error 2010-10-28 13:39:53
101 5 queue 2010-10-28 12:34:58
102 5 index 2010-10-27 10:34:58

I need to eliminate the duplicates in this.

Need to get the record which has maxtime in that.

Output should be

100 3 attorney 2010-10-29 10:03:57
101 5 Error 2010-10-28 13:39:53
102 5 index 2010-10-27 10:34:58

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-29 : 15:46:22

[code]
SELECT a.PKGID, a.PROCESSID, e.NAME, a.AUDITSTAMP
FROM AUDIT a
INNER JOIN
(
SELECT PKGID, PROCESSID, MAX(AUDITSTAMP) AUDITSTAMP
FROM AUDIT
GROUP BY
PKGID, PROCESSID
) x
On x.PKGID = a.PKGID
And x.PROCESSID = a.PROCESSID
And x.AUDITSTAMP = a.AUDITSTAMP
INNER JOIN
EVENT e
ON a.PROCESSID = e.PROCESSID
AND a.RESULTEVENTID = e.EVENTID
WHERE a.AUDITTYPE = 6
AND a.AUDITMSG = 'Routed by user'
and a.PROCESSID in (3,5)
ORDER BY
a.AUDITSTAMP DESC;[/code]
can't tell from your explanation if you need to group and join on PROCESSID or just PKGID. If just PKGID, remove the stuff in red

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 16:34:23

You might also make use of a Ranking function. Here is an example:

SELECT *
FROM 
(
SELECT 
    AUDIT.PKGID,
    AUDIT.PROCESSID, 
    EVENT.NAME,
    AUDIT.AUDITSTAMP,
    ROW_NUMBER() OVER(PARTITION BY AUDIT.PKGID ORDER BY AUDIT.AUDITSTAMP DESC) AS RowNum
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID 
AND AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)
) AS T
WHERE
    RowNum = 1
ORDER BY 
    AUDITSTAMP DESC

reacha
Starting Member

49 Posts

Posted - 2010-10-29 : 16:48:54

IN THE BELOW QUERY IF I MENTION THE DATE CRITERIA LIKE

SELECT *
FROM
(
SELECT
AUDIT.PKGID,
AUDIT.PROCESSID,
EVENT.NAME,
AUDIT.AUDITSTAMP,
ROW_NUMBER() OVER(PARTITION BY AUDIT.PKGID ORDER BY AUDIT.AUDITSTAMP DESC) AS RowNum
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID
AND AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)
) AS T
WHERE
RowNum = 1
AND AUDITSTAMP >= CONVERT(varchar(10), GETDATE() - 12, 110)
AND AUDITSTAMP < CONVERT(varchar(10), GETDATE() - 5, 110)
ORDER BY
AUDITSTAMP DESC

I WAS GETTING THE NO OF RECORDS AS 281

AND IF I MENTION LIKE THIS

SELECT *
FROM
(
SELECT
WF_AUDITLOG.PKGID,
WF_AUDITLOG.PROCESSID,
WF_EVENT.NAME,
WF_AUDITLOG.AUDITSTAMP,
ROW_NUMBER() OVER(PARTITION BY WF_AUDITLOG.PKGID ORDER BY WF_AUDITLOG.AUDITSTAMP DESC) AS RowNum
FROM WF_AUDITLOG INNER JOIN
WF_EVENT ON WF_AUDITLOG.PROCESSID = WF_EVENT.PROCESSID
AND WF_AUDITLOG.RESULTEVENTID = WF_EVENT.EVENTID
WHERE (WF_AUDITLOG.AUDITTYPE = 6) AND (WF_AUDITLOG.AUDITMSG = 'Routed by user')
and WF_AUDITLOG.PROCESSID in (3,5)
AND AUDITSTAMP >= CONVERT(varchar(10), GETDATE() - 12, 110)
AND AUDITSTAMP < CONVERT(varchar(10), GETDATE() - 5, 110)
) AS T
WHERE
RowNum = 1
ORDER BY
AUDITSTAMP DESC

I WAS GETTING THE NO OF RECORDS AS 304

WHAT IS THE DIFFERENCE

Please help me out!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 17:18:53

In the first query, for a given PKGID the greatest date will fall outside of the data predicate. So, applying the WHERE clause in conjunction with the RowNum = 1 will filter out rows that would have fallen into that date range.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 17:20:51

Here is a quick sample that may help to illustrate:

DECLARE @Foo TABLE (ID INT, Num INT, CreateDate DATETIME)

INSERT @Foo VALUES
(1, 1, CURRENT_TIMESTAMP),
(1, 2, CURRENT_TIMESTAMP + 1),
(1, 3, CURRENT_TIMESTAMP - 1),
(1, 4, CURRENT_TIMESTAMP + 2),
(1, 5, CURRENT_TIMESTAMP - 2)


-- See no RowNum = 1
SELECT *
FROM
(
SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CreateDate DESC) AS RowNum
FROM
    @Foo
) AS T
WHERE
    CreateDate BETWEEN CURRENT_TIMESTAMP - 1 AND CURRENT_TIMESTAMP + 1


SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CreateDate DESC) AS RowNum
FROM
    @Foo
WHERE
    CreateDate BETWEEN CURRENT_TIMESTAMP - 1 AND CURRENT_TIMESTAMP + 1

reacha
Starting Member

49 Posts

Posted - 2010-10-29 : 17:51:59

I need to use this query in crystal reports and need to mention the date criteria in the report.

So when i mention the datecriteria in the report it places the date criteria in the outer query.

I need to have lastweek and lastmonth date criteria in my report

Thanks,
reacha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources