# Help with a logic inside WHERE or ORDER BY

I Have this query :

``````;with cte as (
Select tConc.Faturamento
, tFat.TotalFatura
, tCod.Sequencial
, tCodDet.CdCodigoBarras
, tCodDet.Linha
, ValorRecebido
Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
Inner Join Arquivo.CodigoBarras as tCod
On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
Inner Join Arquivo.Faturamento as tFat
On tConc.Faturamento = tFat.CdFaturamento
Where Faturamento In(Select Faturamento
Group By Faturamento
Having Count(CodigoBarras) > 01))
``````

And the result is this one:

``````| Faturamento | TotalFatura | Sequencial | CdCodigoBarras | Linha | ValorRecebido |
|-------------|-------------|------------|----------------|-------|---------------|
| 2458874     | 320,90      | 5783       | 141            | 18393 | 320,90        |
| 2458874     | 320,90      | 5783       | 141            | 18394 | 320,90        |
| 3283899     | 840,50      | 5783       | 141            | 27140 | 840,50        |
| 3283899     | 840,50      | 4397       | 142            | 4010  | 840,50        |
| 3901364     | 89,44       | 21442      | 139            | 23595 | 89,44         |
``````

Long story short, if you notice, there are some equal "faturamento".

What I need is a `distinct` of:

1) If `Faturamento` is the same, we need the biggest `sequencial`. Ok with this, I can order by 3.

2) If `faturamento` and `sequencial` is the same, I need the biggest `cdCodigoBarras`. Ok, again, I can do it with Order by 3,4.

3) If everything is the same, but `valorRecebido`is the same, I need to get the biggest `Linha`. but if `ValorRecebido` is different ( and faturamento, sequencia, cdcodigobarras is the same ), I need the biggest `ValorRecebido`.

Is this something that devs should do in the source code? I really can't think how I can do it with a Query.

I've spend some time tryint to do something with count, but even thinking I'm in the right way, I can past this `when vlaorRecebidois the same, get biggest Linha, or if it's different, the biggest vlaorRecebido`.

• Create an SQL Fiddle with a test database, then we can improve your query. – Gerard H. Pille May 18 '18 at 12:22
• I do not think this will help. I'm using a CTE, so, we need only one table with the results that I posted there ( the table ). The CTE is ok, and the query is OK. I just need a logic to extract the result as needed. – Racer SQL May 18 '18 at 12:48
• Suit yourself, as they say. – Gerard H. Pille May 18 '18 at 13:29
• Clearly the query is not OK or you wouldnâ€™t be asking the question. Creating a fiddle allows someone to help. – Nick.McDermaid May 18 '18 at 21:19

I think you should be able to get what you want with ROW_NUMBER(). Please see the below example.

``````;with cte as (
Select tConc.Faturamento
, tFat.TotalFatura
, tCod.Sequencial
, tCodDet.CdCodigoBarras
, tCodDet.Linha
, ValorRecebido
, RN = ROW_NUMBER() OVER (PARTITION BY  tConc.Faturamento, tCod.Sequencial, tCodDet.CdCodigoBarras, ValorRecebido
ORDER BY tCodDet.Linha DESC, tCodDet.CdCodigoBarras DESC, tCod.Sequencial DESC)
Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
Inner Join Arquivo.CodigoBarras as tCod
On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
Inner Join Arquivo.Faturamento as tFat
On tConc.Faturamento = tFat.CdFaturamento
Where Faturamento In(Select Faturamento