Aleks
asked on
Select distinct
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 ?
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;
ASKER
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.
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.
Try this..
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 z.logid DESC;
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;
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
ASKER
Pawan, got this error:
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'k'.
Vitor, this are the results (Data)
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.
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
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.
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;
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;
ASKER
Scott,
I still get duplicates.
'searchdate' and 'logid' should NOT be compared.
I still get duplicates.
'searchdate' and 'logid' should NOT be compared.
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.
ASKER
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
In fact the USERID is the one that should be unique. The rest of the columns don't matter
Sorry missed AND
Updated
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;
Please let me if it still gives you duplicate Logids..
ASKER
I still got duplicates. the 'a.userid' should be unique.
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.
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
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;
@Author - have u tried my code <<Pawan>> ?
ASKER
I just did, above the results.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Please provide us more info - full requirement , expected results etc.
Are you taking more columns in the select.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
ASKER
Assigned some points to assited solution.
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;
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;
ASKER
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.
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.
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
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