How to get distinct result from left join SQL Server

yqkkidmi 于 2023-02-18 发布在 SQL Server

关注(0)|答案(1)|浏览(160)

My Data is returning result with multiple result with cd.Id2, I would like cd.Id2 to be distinct. If Data result has five cd.Id2 there are the same I would like to only get the first result of every duplicated cd.Id2.

> SELECT Id
>        ,IsIncluded
>        ,LineId
>        ,LineType
>        ,LineNumber
>        ,LineDescription
>          ,SUM(table2.StartBalance) AS StartBalance
>          ,SUM(table1.MoneyIn) AS MoneyIn
>          ,SUM(t1.MoneyOut) AS MoneyOut
> FROM Data AS table1
> LEFT JOIN StartBalanceData table2  ON table1.Id2 = table2.Id

Comments:

Data multiple of same ID (join on first)

StartBalanceData to map with ID

> GROUP BY Id, IsIncluded, LineId, LineType, LineNumber, LineDescription

I tried using LIMIT 1. but it is not supported.

IMAGE OF EXPECTED RESULT

https://pasteboard.co/inNzvmtEI2ux.png

sql-server

来源：https://stackoverflow.com/questions/75136677/how-to-get-distinct-result-from-left-join-sql-server

1条答案

按热度按时间

2g32fytz1#

You could do an OUTER APPLY instead which gets one row only:

SELECT Id
        ,IsIncluded
        ,LineId
        ,LineType
        ,LineNumber
        ,LineDescription
          ,SUM(x.StartBalance) AS StartBalance
          ,SUM(table1.MoneyIn) AS MoneyIn
          ,SUM(t1.MoneyOut) AS MoneyOut
 FROM Data AS table1
 OUTER APPLY (
     SELECT TOP 1 table2.StartBalance
     FROM StartBalanceData table2 
     WHERE table1.Id2 = table2.Id
     ORDER BY <SOMETHING???>
    ) x

Since you were helpful enough to avoid cluttering your question with details like testdata or about which balance data you want to preferably fetch, it's hard to write a complete query, but perhaps this might get you started

赞(0）回复(0）举报 2023-02-18

我来回答

How to get distinct result from left join SQL Server

1条答案

相关问题

热门标签

最新问答