Query to restrict results from left join-CodePudding

I have the following query

select S.id, X.id, 15,15,1 from schema_1.tbl_2638 S
JOIN schema_1.tbl_2634_customid X on S.field_1=x.fullname

That returns the following results, where you can see the first column is duplicated on matches to the 2nd table.

1   1   15  15  1
2   3   15  15  1
2   2   15  15  1
3   5   15  15  1
3   4   15  15  1

I'm trying to get a query that would just give me a single row per 1st ID, and the min value from 2nd ID. So I want a result that would be:

1   1   15  15  1
2   2   15  15  1
3   4   15  15  1

I'm a little rust on my SQL skills, how would I write the query to provide the above result?

CodePudding user response：

From your result you can do,this to achieve your result, for much more compicated structures, you can always take a look at window fucntions

select S.id, MIN(X.id) x_id, 15,15,1 from schema_1.tbl_2638 S
JOIN schema_1.tbl_2634_customid X on S.field_1=x.fullname
GROUP BY 1,3,4,5

window function can be used, need always a outer SELECT

SELECT
    s_id,x_idm a,b,c
FROM
    (select S.id as s_id, X.id as x_id, 15 a ,15 b,1 c
    , ROW_NUMBER() OVER (PARTITION BY S.id ORDER BY X.id ASC) rn
    from schema_1.tbl_2638 S
    JOIN schema_1.tbl_2634_customid X on S.field_1=x.fullname)
WHERE rn = 1

Or as CTE

WITH CTE as (select S.id as s_id, X.id as x_id, 15 a ,15 b,1 c
    , ROW_NUMBER() OVER (PARTITION BY S.id ORDER BY X.id ASC) rn
    from schema_1.tbl_2638 S
    JOIN schema_1.tbl_2634_customid X on S.field_1=x.fullname)
SELECT s_id,x_id,a,b,c FROM CTE WHERE rn = 1