Solved

Select distinct

Posted on 2016-10-03
25
60 Views
Last Modified: 2016-10-03
I have the query below, which may return identical records other than the 'logid' being different.

I want to display only those records which are different, in other words, records that are identical OTHER than the 'logid' which will be different since its an incremental ID.  
The problem is if I remove the logid then I get the records I need, but then I can't order by logid  :$  which I need to have the most recent record at the top.
Is there a way around this ?

SELECT DISTINCT TOP 6
        a.logid ,
        a.firmid ,
        a.userid ,
        a.fileid ,
        a.filetype ,
        a.searchdate ,
        b.FirstNm ,
        b.LastNm ,
        b.Dob ,
        b.DobD
FROM    dbo.LogSearches a
        INNER JOIN Users AS b ON b.UserId = a.fileid
WHERE   a.firmid = 2
        AND a.userid = 1713
        AND a.filetype = 'contact'
ORDER BY a.logid DESC;

Open in new window

0
Comment
Question by:amucinobluedot
  • 10
  • 6
  • 5
  • +2
25 Comments
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 41826382
What you have to think about is what's the criteria to choose between the identical records?

As far as I understood, you want the latest right?
So, in your table, which is the column that stores the record date?

If you give me this information I can draft you a query.

Cheers,
Alex
0
 

Author Comment

by:amucinobluedot
ID: 41826385
I want the most recent 6 records.
the date is recorded on column:  a.searchdate

Which you can use to order it or the logind  ordered DESC would give you the same result.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826472
Try this..

SELECT TOP 6 z.logid , r.* FROM 
(
	SELECT DISTINCT 
			a.firmid ,
			a.userid ,
			a.fileid ,
			a.filetype ,
			a.searchdate ,
			b.FirstNm ,
			b.LastNm ,
			b.Dob ,
			b.DobD
	FROM    dbo.LogSearches a
			INNER JOIN Users AS b ON b.UserId = a.fileid
	WHERE   a.firmid = 2
			AND a.userid = 1713
			AND a.filetype = 'contact'
)r
CROSS APPLY
(	
	SELECT MAX(k.logid) logid
	FROM dbo.LogSearches k
	WHERE 
			k.firmid = r.firmid
			k.userid = r.userid
			k.fileid = r.fileid
			k.filetype = r.filetype
			k.searchdate = r.searchdate
)z
ORDER BY z.logid DESC;

Open in new window



2. 2nd one

SELECT TOP 6 z.logid , r.* FROM 
(
	SELECT DISTINCT 
			a.firmid ,
			a.userid ,
			a.fileid ,
			a.filetype ,
			a.searchdate ,
			b.FirstNm ,
			b.LastNm ,
			b.Dob ,
			b.DobD
	FROM    dbo.LogSearches a
			INNER JOIN Users AS b ON b.UserId = a.fileid
	WHERE   a.firmid = 2
			AND a.userid = 1713
			AND a.filetype = 'contact'
)r
CROSS APPLY
(	
	SELECT MAX(k.logid) logid
	FROM dbo.LogSearches k
	WHERE 
			k.firmid = r.firmid
			k.userid = r.userid
			k.fileid = r.fileid
			k.filetype = r.filetype
			k.searchdate = r.searchdate
)z
ORDER BY r.searchdate DESC;

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41826487
It will help if you can post some data sample. Like you explained isn't really clear to me so I'm asking if removing the DISTINCT keyword it makes any difference for you:
SELECT TOP 6
        a.logid ,
        a.firmid ,
        a.userid ,
        a.fileid ,
        a.filetype ,
        a.searchdate ,
        b.FirstNm ,
        b.LastNm ,
        b.Dob ,
        b.DobD
FROM    dbo.LogSearches a
        INNER JOIN Users AS b ON b.UserId = a.fileid
WHERE   a.firmid = 2
        AND a.userid = 1713
        AND a.filetype = 'contact'
ORDER BY a.logid DESC

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 41826502
Pawan, got this error:

Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'k'.

Vitor, this are the results (Data)

890	2	1713	30638	contact	2016-09-29 13:12:00	Joe	Doe	07/18/2016	2016-07-18 00:00:00.000
887	2	1713	30638	contact	2016-09-28 16:25:00	Joe	Doe	07/18/2016	2016-07-18 00:00:00.000
881	2	1713	30638	contact	2016-09-21 12:12:00	Joe	Doe	07/18/2016	2016-07-18 00:00:00.000
812	2	1713	30746	contact	2016-08-04 09:32:00	Gina	CARDENAS	01/01/2000	2000-01-01 00:00:00.000
755	2	1713	31066	contact	2016-07-13 09:20:00	Maria	DOE	01/02/2000	2000-01-02 00:00:00.000
752	2	1713	31735	contact	2016-07-13 09:13:00	Patti	Doe	06/22/1977	1977-06-22 00:00:00.000

Open in new window


As you can see the logid is different but should NOT be compared in the distinct clause, neither should the 'searchdate' field. the rest should be compared, but not those two because they will be different and used to order BY.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41826510
SELECT  /*logid ,*/ /*optional*/
        firmid ,
        userid ,
        fileid ,
        filetype ,
        searchdate ,
        FirstNm ,
        LastNm ,
        Dob ,
        DobD
FROM (
    SELECT  a.logid ,
            a.firmid ,
            a.userid ,
            a.fileid ,
            a.filetype ,
            a.searchdate ,
            b.FirstNm ,
            b.LastNm ,
            b.Dob ,
            b.DobD ,
            ROW_NUMBER() OVER(PARTITION BY
            a.logid ,
            a.firmid ,
            a.userid ,
            a.fileid ,
            a.filetype ,
            a.searchdate ,
            b.FirstNm ,
            b.LastNm ,
            b.Dob ,
            b.DobD ORDER BY a.logid DESC) AS row_num
    FROM    dbo.LogSearches a
            INNER JOIN Users AS b ON b.UserId = a.fileid
    WHERE   a.firmid = 2
            AND a.userid = 1713
            AND a.filetype = 'contact'
) AS derived
WHERE row_num <= 6
ORDER BY logid DESC;
0
 

Author Comment

by:amucinobluedot
ID: 41826513
Scott,

I still get duplicates.

'searchdate' and 'logid' should NOT be compared.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41826514
Vitor, this are the results (Data)
Is what you're expecting to be returned? If so, then please add some raw data as well so we can have an idea which 6 records we should return from the universe of your data.
0
 

Author Comment

by:amucinobluedot
ID: 41826519
Please see above. Those 6 show 3 duplicate records. Same name and same DOB.
In fact the USERID is the one that should be unique. The rest of the columns don't matter
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826521
Sorry missed AND

Updated

SELECT TOP 6 z.logid , r.* FROM 
(
	SELECT DISTINCT 
			a.firmid ,
			a.userid ,
			a.fileid ,
			a.filetype ,
			a.searchdate ,
			b.FirstNm ,
			b.LastNm ,
			b.Dob ,
			b.DobD
	FROM    dbo.LogSearches a
			INNER JOIN Users AS b ON b.UserId = a.fileid
	WHERE   a.firmid = 2
			AND a.userid = 1713
			AND a.filetype = 'contact'
)r
CROSS APPLY
(	
	SELECT MAX(k.logid) logid
	FROM dbo.LogSearches k
	WHERE 
			k.firmid = r.firmid AND 
			k.userid = r.userid AND 
			k.fileid = r.fileid AND 
			k.filetype = r.filetype AND 
			k.searchdate = r.searchdate
)z
ORDER BY z.logid DESC;

Open in new window

0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826525
Please let me if it still gives you duplicate Logids..
0
 

Author Comment

by:amucinobluedot
ID: 41826529
I still got duplicates. the 'a.userid' should be unique.

890	2	1713	30638	contact	2016-09-29 13:12:00	Joe	Doe	07/18/2016	2016-07-18 00:00:00.000
887	2	1713	30638	contact	2016-09-28 16:25:00	Joe	Doe	07/18/2016	2016-07-18 00:00:00.000
881	2	1713	30638	contact	2016-09-21 12:12:00	Joe	Doe	07/18/2016	2016-07-18 00:00:00.000
812	2	1713	30746	contact	2016-08-04 09:32:00	Gina	CARDENAS	01/01/2000	2000-01-01 00:00:00.000
755	2	1713	31066	contact	2016-07-13 09:20:00	Maria	DOE	01/02/2000	2000-01-02 00:00:00.000
752	2	1713	31735	contact	2016-07-13 09:13:00	Patti	Doe	06/22/1977	1977-06-22 00:00:00.000

Open in new window


As you can see Joe Doe is listed 3 times, the fileid is the same.

I apologize is not 'userid' that is unique, its the 'fileid'

I made the query simpler based on yours but I still get Joe Doe listed 3 times.

SELECT TOP 6 z.logid , r.* FROM 
(
	SELECT DISTINCT 
			a.fileid ,
			a.searchdate ,
			b.FirstNm ,
			b.LastNm ,
			b.DobD
	FROM    dbo.LogSearches a
			INNER JOIN Users AS b ON b.UserId = a.fileid
	WHERE   a.firmid = 2
			AND a.userid = 1713
			AND a.filetype = 'contact'
)r
CROSS APPLY
(	
	SELECT MAX(k.logid) logid
	FROM dbo.LogSearches k
	WHERE 
			k.fileid = r.fileid AND 
			k.searchdate = r.searchdate
)z
ORDER BY z.logid DESC;

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826532
@Author - have u tried my code <<Pawan>> ?
0
 

Author Comment

by:amucinobluedot
ID: 41826533
I just did, above the results.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41826538
Those 6 show 3 duplicate records. Same name and same DOB.
Your definition of duplicate is not the same for a database and I think that's why a lot of confusion is still here. Also, that's why posting sample data of the source and the expected results helps a lot who is trying to give you a solution.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 50 total points
ID: 41826546
Try this:
SELECT TOP 6
        MAX(a.logid) LogID,
        a.firmid,
        a.userid,
        a.fileid,
        a.filetype,
        MAX(a.searchdate),
        b.FirstNm,
        b.LastNm,
        b.Dob,
        b.DobD
FROM    dbo.LogSearches a
        INNER JOIN Users AS b ON b.UserId = a.fileid
WHERE   a.firmid = 2
        AND a.userid = 1713
        AND a.filetype = 'contact'
GROUP BY a.userid,
        a.fileid,
        a.filetype,
        b.FirstNm,
        b.LastNm,
        b.Dob,
        b.DobD
ORDER BY 1 DESC

Open in new window

0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826547
Vitor is correct. You are running a different query. My query should return 7 columns only, Your output is  giving us more column than that.

Please provide us more info - full requirement , expected results etc.

Are you taking more columns in the select.
0
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 400 total points
ID: 41826549
May be this

SELECT TOP 6 z.logid , r.* FROM 
(
	SELECT DISTINCT 
			a.firmid ,
			a.userid ,
			a.fileid ,
			a.filetype ,
			MAX(a.searchdate) OVER (PARTITION BY a.firmid ,a.userid, a.fileid , a.filetype , b.FirstNm  , b.LastNm ,b.Dob, b.DobD) searchdate,
			b.FirstNm ,
			b.LastNm ,
			b.Dob ,
			b.DobD
	FROM    dbo.LogSearches a
			INNER JOIN Users AS b ON b.UserId = a.fileid
	WHERE   a.firmid = 2
			AND a.userid = 1713
			AND a.filetype = 'contact'
)r
CROSS APPLY
(	
	SELECT MAX(k.logid) logid
	FROM dbo.LogSearches k
	WHERE 
			k.firmid = r.firmid AND 
			k.userid = r.userid AND 
			k.fileid = r.fileid AND 
			k.filetype = r.filetype AND 
			k.searchdate = r.searchdate
)z
ORDER BY z.logid DESC;

Open in new window

0
 
LVL 30

Assisted Solution

by:Alexandre Simões
Alexandre Simões earned 50 total points
ID: 41826557
Hi mate,
I think what you need is something more like the bellow.

A warning tho, I couldn't test this so it's possible that I have some typos.
The idea is that we do a groupby with a max date and without the id.
Then we re-join with the same table, including the max date field.

This strategy won't work if you have two logs exactly with the same date...
SELECT TOP(6) 
        t.logid, 
        t.firmid ,
        t.userid ,
        t.fileid ,
        t.filetype ,
        t.SearchDate,
        t.FirstNm ,
        t.LastNm ,
        t.Dob ,
        t.DobD
FROM (
      SELECT 
        a.firmid ,
        a.userid ,
        a.fileid ,
        a.filetype ,
        MAX(a.searchdate) MaxSearchDate,
        b.FirstNm ,
        b.LastNm ,
        b.Dob ,
        b.DobD
      FROM    dbo.LogSearches a
        INNER JOIN Users AS b ON b.UserId = a.fileid
      WHERE   a.firmid = 2
        AND a.userid = 1713
        AND a.filetype = 'contact'
      GROUP BY 
        a.firmid ,
        a.userid ,
        a.fileid ,
        a.filetype ,
        b.FirstNm ,
        b.LastNm ,
        b.Dob ,
        b.DobD
) r
INNER JOIN dbo.LogSearches t
ON 
        r.firmid = t.firmid AND
        r.userid = t.userid AND
        r.fileid = t.fileid AND
        r.filetype = t.filetype AND
        r.FirstNm = t.FirstNm AND
        r.LastNm = t.LastNm AND
        r.Dob = t.Dob AND
        r.DobD = t.DobD AND
        r.MaxSearchDate = t.SearchDate

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 41826562
Victor:

Msg 8120, Level 16, State 1, Line 24
Column 'dbo.LogSearches.firmid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Alexandre:

Msg 207, Level 16, State 1, Line 44
Invalid column name 'FirstNm'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'LastNm'.
Msg 207, Level 16, State 1, Line 46
Invalid column name 'Dob'.
Msg 207, Level 16, State 1, Line 47
Invalid column name 'DobD'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'FirstNm'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'LastNm'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Dob'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'DobD'.


Pawan: Seems to have worked. I will test and confirm.
0
 

Author Comment

by:amucinobluedot
ID: 41826568
Pawan's code worked. the rest gave errors. I will assign points to Pawan since its the only answer that worked with no errors. I will post a new sql that is very similar to this query in one minute.
0
 

Author Closing Comment

by:amucinobluedot
ID: 41826570
Assigned some points to assited solution.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41826571
Sorry, I meant to remove logid from the PARTITION BY; and I will remove searchdate also.

SELECT  /*logid ,*/ /*optional*/
        firmid ,
        userid ,
        fileid ,
        filetype ,
        searchdate ,
        FirstNm ,
        LastNm ,
        Dob ,
        DobD
FROM (
    SELECT  *,
            ROW_NUMBER() OVER(PARTITION BY
            a.firmid ,
            a.userid ,
            a.fileid ,
            a.filetype ,
            b.FirstNm ,
            b.LastNm ,
            b.Dob ,
            b.DobD ORDER BY a.logid DESC) AS row_num
    FROM    dbo.LogSearches a
            INNER JOIN Users AS b ON b.UserId = a.fileid
    WHERE   a.firmid = 2
            AND a.userid = 1713
            AND a.filetype = 'contact'
) AS derived
WHERE row_num <= 6
ORDER BY logid DESC;
0
 

Author Comment

by:amucinobluedot
ID: 41826581
Scott:  I tried your code above and got this error:

Msg 8156, Level 16, State 1, Line 52
The column 'UserId' was specified multiple times for 'derived'.

I used Pawan's code and it worked fine.

Just posted a similar query.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41827519
Sorry, just could see the comments now. I've forgot to add one column to the GROUP BY clause:
SELECT TOP 6
        MAX(a.logid) LogID,
        a.firmid,
        a.userid,
        a.fileid,
        a.filetype,
        MAX(a.searchdate),
        b.FirstNm,
        b.LastNm,
        b.Dob,
        b.DobD
FROM    dbo.LogSearches a
        INNER JOIN Users AS b ON b.UserId = a.fileid
WHERE   a.firmid = 2
        AND a.userid = 1713
        AND a.filetype = 'contact'
GROUP BY a.firmid,
        a.userid,
        a.fileid,
        a.filetype,
        b.FirstNm,
        b.LastNm,
        b.Dob,
        b.DobD
ORDER BY 1 DESC

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now