0

Sub-query returning more than 1 row.

My current SQL Server query looks like this

SELECT DISTINCT AST.AssetName
    ,ReleaseDt
    ,ExpiresDt
    ,TicketNumber
    ,ChangeDt
    ,ChangeReasonCd
FROM pmm.pmmreleaserequest PRR WITH (NOLOCK)
LEFT JOIN pmm.PmmManagedAccount AS PMA WITH (NOLOCK) ON PRR.ManagedAccountID = PMA.ManagedAccountID
LEFT JOIN dbo.ManagedEntity AS ME WITH (NOLOCK) ON PRR.ManagedSystemID = ME.ManagedEntityID
LEFT JOIN dbo.Asset AS AST WITH (NOLOCK) ON ME.AssetID = AST.AssetID
LEFT JOIN pmm.PmmLogChange AS PLC WITH (NOLOCK) ON PRR.ManagedAccountID = PLC.ManagedAccountID
    AND PRR.ExpiresDt < PLC.ChangeDt
ORDER BY PLC.ChangeDt ASC

And currently my output looks like as below.

AssetName       ReleaseDt           ExpiresDt           TicketNumber    ChangeDt            ChangeReasonCd    
DummyAsset66    2020-05-02 17:45:38 2020-05-02 17:45:52 dummyticketx1   2020-05-02 17:50:06 U    
***DummyAsset66 2020-05-02 17:45:38 2020-05-02 17:45:52 dummyticketx1   2020-05-02 18:26:06 U***    
DummyAsset66    2020-05-02 18:23:12 2020-05-02 18:23:59 dummyticketx2   2020-05-02 18:26:06 U

I don't want to print the second row alone but still need the 3rd row from the output, below Left Join is returning more than one row. I want to take the first row alone.

LEFT JOIN pmm.PmmLogChange AS PLC With (nolock) ON PRR.ManagedAccountID = PLC.ManagedAccountID and PRR.ExpiresDt < PLC.ChangeDt
James Z
  • 12,209
  • 10
  • 24
  • 44
tata
  • 33
  • 3
  • 2
    See [Bad Habits to kick - putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) - it is *not recommended* to use this everywhere - quite the contrary! – marc_s May 03 '20 at 06:42
  • I can't figure out why you are using `LEFT JOIN`. Your first column is for the asset name and yet it can be `NULL` with your logic. – Gordon Linoff May 03 '20 at 12:46

1 Answers1

0

If I get this correctly, you want to deduplicate your result set by certain set of columns (apparently by AssetName and TicketNumber). Since SQL Server lacks DISTINCT ON operator, I suggest you use the following trick as described here

You basically populate each row in your projection with row number like this ROW_NUMBER() OVER(PARTITION BY <whatever columns you consider as deduplication keys like AssetName,TicketNumber, ...>) then filter out all rows with ROW_NUMBER() > 1 instead of using DISTINCT operator. And btw, I agree with @marc_s, you shouldn't be using NOLOCK like you do.

But if the problem is only with last join you can replace it with subquery like this (SELECT TOP 1 ... FROM pmm.PmmLogChange AS PLC WHERE PRR.ManagedAccountID = PLC.ManagedAccountID AND PRR.ExpiresDt < PLC.ChangeDt ORDER BY PLC.ChangeDt ASC) AS mngac

madmanul
  • 420
  • 4
  • 14
  • LEFT JOIN pmm.PmmLogChange AS PLC With (nolock) ON PRR.ManagedAccountID = PLC.ManagedAccountID and PRR.ExpiresDt < PLC.ChangeDt. this condition returning more then one row and causing issue for me. I want only the first row after order by ASC. – tata May 03 '20 at 07:16
  • It will be helpful if you can provide the modified query and send me the query, as am not expert in SQL query language – tata May 03 '20 at 07:25
  • is that suppose to be the first arbitrary row from `pmm.PmmLogChange` table that fits your condition? I mean don't you have to select this single row by certain criteria like one with the largest ChangeDt < ExpiresDt or any of those – madmanul May 03 '20 at 07:25
  • right From pmm.PmmLogChange table I have to match first ManagedAccountID which list more then one row and again compare the ChangeDt should be > then ExpireDt again it will return more then one row Order it by ChangeDt ASC get only the first row – tata May 03 '20 at 07:33
  • Syntax Error SELECT DISTINCT AST.AssetName ,ReleaseDt ,ExpiresDt,TicketNumber,ChangeDt,ChangeReasonCd FROM pmm.pmmreleaserequest PRR WITH (NOLOCK) LEFT JOIN pmm.PmmManagedAccount AS PMA WITH (NOLOCK) ON PRR.ManagedAccountID = PMA.ManagedAccountID LEFT JOIN dbo.ManagedEntity AS ME WITH (NOLOCK) ON PRR.ManagedSystemID = ME.ManagedEntityID LEFT JOIN dbo.Asset AS AST WITH (NOLOCK) ON ME.AssetID = AST.AssetID (select top 1 ChangeDt from pmm.PmmLogChange AS PLC where PRR.ManagedAccountID = PLC.ManagedAccountID AND PRR.ExpiresDt < PLC.ChangeDt) ORDER BY PLC.ChangeDt ASC – tata May 03 '20 at 07:50