Syntax Error on Distinct inside a Join in SQL Server

Question

I have a query and I need to display distinct values from a join. I have this kind of SQL query

 select 
     a.member_id, b.registered_time 
 from 
     b 
 left join 
     (select distinct a.member_id from a) on b.mirror_id = a.mirror_id
 order by 
     b.registered_time desc;

But this query returns me a syntax error

Incorrect syntax near the keyword 'on'

How can I fix that?

I also tried doing this query but it returns an error

Invalid column name 'mirror_id'

Code:

select  
    a.member_id, b.registered_time 
from  
    b 
left join 
    (select distinct a.member_id from a) a on b.mirror_id = a.mirror_id
order by 
    b.registered_time desc;

I also tried doing this kind

select  
    a.member_id, b.registered_time 
from 
    b 
left join 
    (select distinct a.member_id from a) AP on b.mirror_id = a.mirror_id
order by 
    b.registered_time desc;

but it returns an error that multipart identifier couldn't be bound

I have these kind of result from my join:

For example I have these two tables

Table a

Table 2

But I need to display only one member_id with the latest registered_time

Are you using SQL Server or MySQL? (It actually doesn't matter for this problem but you should tag the question appropriately.) — Gordon Linoff, Oct 04 '17 at 02:33
We can help you correct the syntax to avoid he error messages - BUT we cannot identify if the query will provide the wanted result; UNLESS you provide `sample data` and `expected result`. — Paul Maxwell, Oct 04 '17 at 03:05

Paul Maxwell · Answer 1 · 2017-10-04T07:25:12.267

2

Original (reformatted):

select  a.member_id, b.registered_time 
from b 
left join  (select distinct a.member_id from a) 
     on b.mirror_id = a.mirror_id
order by b.registered_time desc;

Step 1, place the alias AFTER the subquery:

select  a.member_id, b.registered_time 
from b 
left join  (select distinct a.member_id from a) a
------------------------------------------------^
     on b.mirror_id = a.mirror_id
order by b.registered_time desc;

Step 2, INCLUDE the column mirror_id inside the subquery

select  a.member_id, b.registered_time 
from b 
left join  (select distinct a.member_id, mirror_id from a) a
---------------------------------------^^^^^^^^^^^
     on b.mirror_id = a.mirror_id
order by b.registered_time desc;

At this point the query should work, but I cannot tell if it is functionally what you want unless you provide sample data and expected result.

EDIT (2)

a simple GROUP BY may be used to arrive at a max date per member:

SELECT 
       a.member_id 
     , max(b.registered_time) registered_time 
FROM a 
INNER JOIN b ON a.mirror_id = b.mirror_id 
GROUP BY 
      a.member_id

If you need whole rows from table a, then the row_number() approach is better:

SELECT d.*
FROM (
      SELECT
            a.*
          , b.registered_time
          , row_number() over(partition by a.member_id order by b.registered_time DESC) rn
      FROM a
      INNER JOIN b ON a.mirror_id = b.mirror_id
      ) d
WHERE d.rn = 1

edited Oct 04 '17 at 07:25

answered Oct 04 '17 at 03:03

Paul Maxwell

33,002
3
32
51

This query is working fine now but it returns duplicated data like this: member_id || registered_time 201602231785170 || 2016-06-23 01:59:59.620 , 201602231785170 || 2016-06-23 02:00:09.417 , 201602231785170 || 2016-06-23 02:00:17.890 , 201602231785170 || 2016-06-23 02:00:23.870 , 201612090120211 || 2016-06-29 05:30:57.197 , 201606155818625 || 2016-07-07 09:20:51.417 , 201602231785170 || 2016-07-07 09:20:51.417 It has duplicated member_id but I need to display distinct member_id with its latest registered_time – bleyk Oct 04 '17 at 03:10
I cannot see into your data or your mind... if you want more assistance you must provide `sample data` and `expected result`. Look back at your question, it is purely about the error messages. – Paul Maxwell Oct 04 '17 at 03:14
the roe_number() and over returns me an error that mirror_id and registered_time is invalid column – bleyk Oct 04 '17 at 05:13
I left out `from b` in that subquery (+ I also updated the query above) – Paul Maxwell Oct 04 '17 at 05:54
Its working now, but I still have duplicate member_id. I am connecting the two tables by mirror_id – bleyk Oct 04 '17 at 06:12
I need to have no duplicate meber id with the latest time – bleyk Oct 04 '17 at 06:12
for the last time.... provide `sample data` and `expected result` (from that data) . ROW_NUMBER() OVER() is by far the best method to achieve "latest" once you provide some data I can prove it, but it isn't my responsibility to provide data for your question. – Paul Maxwell Oct 04 '17 at 06:25
try reading this question https://stackoverflow.com/questions/38505558/sql-select-distinct-column-and-latest-date note how it provided data and expected result... and how easily it was answered. – Paul Maxwell Oct 04 '17 at 06:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/155874/discussion-between-bleykfaust-and-used-by-already). – bleyk Oct 04 '17 at 06:49

Gordon Linoff · Answer 2 · 2017-10-04T11:30:44.920

0

You need a table alias:

select  a.member_id, b.registered_time
from b left join 
     (select distinct a.member_id
      from a
     ) a
-------^
     on b.mirror_id = a.mirror_id
order by b.registered_time desc;

EDIT:

Well, you need the mirror_id to do the join. Why do you need the distinct? In any case, you can try:

select  a.member_id, b.registered_time
from b left join 
     (select a.mirror_id, min(a.member_id) as member_id
      from a
      group by a.mirror_id
     ) a
     on b.mirror_id = a.mirror_id
order by b.registered_time desc;

edited Oct 04 '17 at 11:30

answered Oct 04 '17 at 02:33

Gordon Linoff

1,242,037
58
646
786

I tried using a also as an alias but it shows "Invalid column name 'mirror_id'" – bleyk Oct 04 '17 at 02:36
It returns me another error that says like this "SQL Error (8120): Column 'membermirror profile.mirror_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – bleyk Oct 04 '17 at 02:59
I need distinct because I want to display distinct member_id only with latest registered_time. Is it possibele? – bleyk Oct 04 '17 at 05:23
@bleykFaust . . . I would suggest that you ask another question with sample data, desired results, and a clear explanation of what you want to do. – Gordon Linoff Oct 04 '17 at 11:29

Syntax Error on Distinct inside a Join in SQL Server

2 Answers2