# Join row\_number over function in dremio getting Wrong Result

**URL:** <https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257>\
**Category:** Uncategorized\
**Created:** [April 24, 2019, 6:28am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257 "2019-04-24T06:28:18Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [April 24, 2019, 6:28am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/1 "2019-04-24T06:28:18Z")

</div>

I have used this query in SSMS and get correct Result  
select EntityId,EntityName,WellType,ProductionDate,EffectiveDate,Field,CondyMeasured,OilMeasured,WaterMeasured,CGRComponentRatio,WGRComponentRatio from  
(select EntityId,EntityName,WellType,ProductionDate,EffectiveDate,Field,CondyMeasured,OilMeasured,WaterMeasured,CGRComponentRatio,WGRComponentRatio  
,row\_number() over(partition by EntityId,ProductionDate order by EntityId,ProductionDate,EffectiveDate desc) as roworder from  
(select \* from (SELECT FDCWellRecords.EntityId,FDCWellRecords.WellType,FDCWellRecords.ProductionDate,FDCWellRecords.EntityName,Field,CondyMeasured,OilMeasured,WaterMeasured,EffectiveDate,CGRComponentRatio,  
WGRComponentRatio FROM FDCWellRecords inner Join FDCComponentRatio on FDCWellRecords.EntityId = FDCComponentRatio.EntityId ) temp where temp.EffectiveDate is not null and temp.WellType = ‘Effluent’) temp1 where ProductionDate \>= EffectiveDate) temp2 where roworder = 1

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/dc50dd9e6703e33038e29c91c4a787ddb3ab32e3.png)

But same query in Dremio getting Wrong Result here is the attached screenshots

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/0/052748f74fdddcf718ed1e0d2623a0ac64738aee.png)

I am using Dremio 3.1.1-201901281837360699-30c9d74

here is attached Query Profile  
[e1509cdf-dd55-458f-b464-20b979d6d568.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/0/045246777d2841ec645014948da785446cd37a94.zip) (22.1 KB)

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [April 24, 2019, 7:34am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/2 "2019-04-24T07:34:06Z")

</div>

i have also tested on latest Dremio version 3.1.10-201904162146020182-adf690d but still i am getting diffrent result

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/a/a2dc9728990d75aa4640f196e73616e051749d99.png)

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [April 24, 2019, 12:57pm UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/3 "2019-04-24T12:57:06Z")

</div>

Any Alternative we can use instaid of Row \_function() i see similar Thred here

> [@Error when running window ROW\_NUMBER function](https://community.dremio.com/t/error-when-running-window-row-number-function/360/12):
>
> Hi Chris, We announce new releases here. You can expect ~1/month for the next several months. Kelly

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [April 25, 2019, 9:38pm UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/4 "2019-04-25T21:38:53Z")

</div>

@Vikash_Singh,

The SQL in the profile is different from what you supplied in the comments. Please run the below query in Dremio and in SSMS and include a screenshot of the results with the `rownumber`, `ProductionDate` and `EffectiveDate` clearly visible in each.

> ```
> select
> EntityId,
> EntityName,
> WellType,
> ProductionDate,
> EffectiveDate,
> Field,
> CondyMeasured,
> OilMeasured,
> WaterMeasured,
> CGRComponentRatio,
> WGRComponentRatio 
> from
> (select
> EntityId,
> EntityName,
> WellType,
> ProductionDate,
> EffectiveDate,
> Field,
> CondyMeasured,
> OilMeasured,
> WaterMeasured,
> CGRComponentRatio,
> WGRComponentRatio ,
> row_number() over(partition 
> by
> EntityId,
> ProductionDate 
> order by
> EntityId,
> ProductionDate,
> EffectiveDate desc) as roworder 
> from
> (select
> * 
> from
> (SELECT
> FDCWellRecords.EntityId,
> FDCWellRecords.WellType,
> FDCWellRecords.ProductionDate,
> FDCWellRecords.EntityName,
> Field,
> CondyMeasured,
> OilMeasured,
> WaterMeasured,
> EffectiveDate,
> CGRComponentRatio,
> WGRComponentRatio 
> FROM
> FDCWellRecords 
> inner Join
> FDCComponentRatio 
> on FDCWellRecords.EntityId = FDCComponentRatio.EntityId ) temp 
> where
> temp.EffectiveDate is not null 
> and temp.WellType = ‘Effluent’
> ) temp1 
> where
> ProductionDate >= EffectiveDate) temp2 
> where
> roworder = 1
> 
> ```

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [April 26, 2019, 3:59am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/5 "2019-04-26T03:59:55Z")

</div>

Here is the attached screenshots

## SSMS

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/0/010ee1e18e545afeb85b83d72ea0f2da67c1571f.png)

## dremio

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/5/53a9cbba94cec8de02d537e63ea34ec8c6edcccf.png) [660a0d58-d05d-4ac3-b423-c95f51e14d6d.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/e/e03947226e134b32698578c67a1b538bfa51b292.zip) (16.8 KB)

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [April 30, 2019, 5:19am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/6 "2019-04-30T05:19:02Z")

</div>

Any suggestion on above Query?

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [April 30, 2019, 4:35pm UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/7 "2019-04-30T16:35:27Z")

</div>

Is see that EffectiveDate is different in Dremio, but you are executing a Preview, not a Run. **Please run the query** and check the results:  
 ![21%20AM](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/a/aec944c6ff0924549501932ce0ed42ba1ec44aa3.png)

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [May 1, 2019, 3:45am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/8 "2019-05-01T03:45:36Z")

</div>

i have triyed Run too but getting same result as previous there was not changing in query  
result

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/7/7a322773ec9f6bea94f4b665554159817b2523c6.png)

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [May 1, 2019, 3:56am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/9 "2019-05-01T03:56:49Z")

</div>

Please attach the profile for this Run so we can examine what query Dremio is pushing down into the SQL Server source.

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [May 1, 2019, 4:17am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/10 "2019-05-01T04:17:50Z")

</div>

thanks ,here is attached Profile for above query[a035c4da-3e53-4833-a679-261a3956e2b5.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b45b0ddfacb02341740b30e54be0bc73bb6ab03e.zip) (22.5 KB)

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [May 1, 2019, 3:52pm UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/11 "2019-05-01T15:52:28Z")

</div>

This is the query that Dremio is pushing down into the MSSQL source:

> ```
> SELECT
> [EntityId] COLLATE LATIN1_GENERAL_BIN2 AS [EntityId],
> [EntityName] COLLATE LATIN1_GENERAL_BIN2 AS [EntityName],
> CAST('Effluent' COLLATE LATIN1_GENERAL_BIN2 AS VARCHAR(8000)) AS [WellType],
> [ProductionDate],
> [EffectiveDate],
> [Field] COLLATE LATIN1_GENERAL_BIN2 AS [Field],
> [CondyMeasured],
> [OilMeasured],
> [WaterMeasured],
> [CGRComponentRatio],
> [WGRComponentRatio] 
> FROM
> (SELECT
> [EntityId] COLLATE LATIN1_GENERAL_BIN2 AS [EntityId],
> [EntityName] COLLATE LATIN1_GENERAL_BIN2 AS [EntityName],
> CAST('Effluent' COLLATE LATIN1_GENERAL_BIN2 AS VARCHAR(8000)) AS [WellType],
> [ProductionDate],
> [EffectiveDate],
> [Field] COLLATE LATIN1_GENERAL_BIN2 AS [Field],
> [CondyMeasured],
> [OilMeasured],
> [WaterMeasured],
> [CGRComponentRatio],
> [WGRComponentRatio],
> ROW_NUMBER() OVER (PARTITION 
> BY
> [EntityId] COLLATE LATIN1_GENERAL_BIN2,
> [ProductionDate] 
> ORDER BY
> CASE 
> WHEN [EntityId] COLLATE LATIN1_GENERAL_BIN2 IS NULL THEN 1 
> ELSE 0 
> END,
> [EntityId] COLLATE LATIN1_GENERAL_BIN2,
> CASE 
> WHEN [ProductionDate] IS NULL THEN 1 
> ELSE 0 
> END,
> [ProductionDate],
> CASE 
> WHEN [EffectiveDate] IS NULL THEN 1 
> ELSE 0 
> END DESC,
> [EffectiveDate]) AS [roworder] 
> FROM
> (SELECT
> [EntityId] COLLATE LATIN1_GENERAL_BIN2 AS [EntityId],
> CAST('Effluent' COLLATE LATIN1_GENERAL_BIN2 AS VARCHAR(8000)) AS [WellType],
> [ProductionDate],
> [EntityName] COLLATE LATIN1_GENERAL_BIN2 AS [EntityName],
> [Field] COLLATE LATIN1_GENERAL_BIN2 AS [Field],
> [CondyMeasured],
> [OilMeasured],
> [WaterMeasured],
> [EffectiveDate],
> [CGRComponentRatio],
> [WGRComponentRatio] 
> FROM
> (SELECT
> [FDCWellRecords].[EntityId] COLLATE LATIN1_GENERAL_BIN2 AS [EntityId],
> [FDCWellRecords].[WellType] COLLATE LATIN1_GENERAL_BIN2 AS [WellType],
> [FDCWellRecords].[ProductionDate],
> [FDCWellRecords].[EntityName] COLLATE LATIN1_GENERAL_BIN2 AS [EntityName],
> [FDCWellRecords].[Field] COLLATE LATIN1_GENERAL_BIN2 AS [Field],
> [FDCWellRecords].[CondyMeasured],
> [FDCWellRecords].[OilMeasured],
> [FDCWellRecords].[WaterMeasured],
> [FDCComponentRatio].[EffectiveDate],
> [FDCComponentRatio].[CGRComponentRatio],
> [FDCComponentRatio].[WGRComponentRatio] 
> FROM
> [tourmalineqadb].[dbo].[FDCWellRecords] 
> INNER JOIN
> [tourmalineqadb].[dbo].[FDCComponentRatio] 
> ON [FDCWellRecords].[EntityId] COLLATE LATIN1_GENERAL_BIN2 = [FDCComponentRatio].[EntityId] COLLATE LATIN1_GENERAL_BIN2) AS [t] 
> WHERE
> [t].[WellType] COLLATE LATIN1_GENERAL_BIN2 = 'Effluent' COLLATE LATIN1_GENERAL_BIN2 
> AND [t].[EffectiveDate] IS NOT NULL
> ) AS [t] 
> WHERE
> [ProductionDate] >= [EffectiveDate]) AS [t] 
> WHERE
> [roworder] = 1
> 
> ```

Does this give the same results as Dremio when you submit it with SSMS (I’m assuming it will).

Do you have any special options set, like “Enable legacy dialect”?  
 ![advanced-options](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/7/7ca24c46932bea3e26ea6d9768fbfd69279f694a.png)

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [May 3, 2019, 1:32pm UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/12 "2019-05-03T13:32:01Z")

</div>

Hello Ben,

You are right SSMS also Queries same Results and also I did not enabled any Legacy dialect setting

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/6/632eba1e4ee2ecad914381c534b1c7225465533b.png)

---

<div class="post-metadata">

**Author:** ![Vikash\_Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vikash_singh/32/1069_2.png) [@Vikash\_Singh](https://community.dremio.com/u/Vikash_Singh)\
**Post date:** [May 4, 2019, 3:58am UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/13 "2019-05-04T03:58:20Z")

</div>

![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/7/7b54f554b883c51d00c077ef7d2a25de05f5b391.png)

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [September 5, 2019, 3:10pm UTC](https://community.dremio.com/t/join-row-number-over-function-in-dremio-getting-wrong-result/3257/14 "2019-09-05T15:10:04Z")

</div>

Hi @Vikash_Singh, apologies for the very late response here.

Dremio orders the null values differently than SQL Server; Dremio ranks them “highest” while [MSSQL ranks them lowest](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#arguments). So when Dremio pushes down a query into the MSSQL source, it changes the query to make sure the records sent back are ordered as Dremio would order them.

That’s why are you seeing the difference.