Out of 3 same/ similar rows, remove two and keep one

Question

Post reply

Out of 3 same/ similar rows, remove two and keep one

JeremyU

SSChasing Mays

Points: 640
More actions
June 26, 2023 at 6:32 pm

#4219989

Hello everyone,
I would like to ask someone for an advice. I am not even a 100% sure it is doable, but it might be.
I have a table that returns the same or almost the same rows of information often times 3 times.
Basically there are 3 types of Transactions: 1. GL Posted - means it is paid. 2. GL Accrual Reversed. 3. GL Accrual.

I need to create some kind of logic that if for one [Voucher Num] there is [GL Posted] for [G/L Transaction Type Desc] we want just to keep this row and remove two rows that have GL Accrual Reversed and GL Accrual rows.

Does someone know if this can be done in SQL?
SELECT
[Freight Bill ID],
[Voucher Num],
[G/L Transaction Type Desc],
[Freight Bill Num],
*
FROM final_ouput

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

By 'remove', do you mean you want these rows deleted from the database?

Or simply a query which removes them from the rows which are selected?

Please provide your sample data in a consumable form (CREATE TABLE / INSERT) if you would like a coded solution.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

DELETE fo
  FROM final_output fo
 WHERE EXISTS(SELECT 1
                FROM final_output fo2
               WHERE fo2.[Voucher Num] = fo.[Voucher Num]
                 AND fo2.[G/L Transaction Type Desc] = 'GL Posted')
   AND fo2.[G/L Transaction Type Desc] IN('GL Accrual Reversed', 'GL Accrual')

JeremyU SSChasing Mays Points: 640 More actions · Answer 3

@Phil Parkin, simply a query which removes them from the rows that are selected.

I don't have any CREATE statements. I am just selecting rows from CTE. But the way how data is there, we have multiple rows for the same Voucher and I need only those that are 'GL Posted'. But in case there are some 'Accrual Reversed' and 'Accruals' I want to keep them (if they didn't move to GL Posted).

SELECT

[Freight Bill ID],

[Voucher Num],

[G/L Transaction Type Desc],

[Freight Bill Num],

*

FROM final_ouput

JeremyU SSChasing Mays Points: 640 More actions · Answer 4

@jonathan-2 thank you, but I don't think I should delete records from the table, I think I should just not select them in a query, is that possible?

Emperor100 SSCrazy Points: 2173 More actions · Answer 5

Try this:

Replace * with your required columns

SELECT *
FROM final_output fo
WHERE fo.[G/L Transaction Type Desc] = 'GL Posted'
and fo.[Freight Bill Number] is not null

You can add more conditions in the WHERE to SELECT the row that you need. An alternate is use ROW_NUMBER() function to assign row number 1 (one) to the row that you need and then eliminate the others with rownumber > 1.

=======================================================================

JeremyU SSChasing Mays Points: 640 More actions · Answer 6

Emperor100 thank you, but that's not what I am trying to achieve. I am not trying to remove NULLs. I very often time have the same row twice but one is GL Posted and one Accruals or GL Accrual Reversed (for example) and if it happens I want to keep GL Posted only. If there is no GL Posted, I want keep Accruals. It has nothing to do with NULLs. It may be NULL of course and it may not be.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

SELECT *
  FROM final_output fo
 WHERE [G/L Transaction Type Desc] = 'GL Posted'
    OR ([G/L Transaction Type Desc] <> 'GL Posted'
          AND NOT EXISTS(SELECT 1
                           FROM final_output fo2
                          WHERE fo2.[Voucher Num] = fo.[Voucher Num]
                            AND fo2.[G/L Transaction Type Desc] = 'GL Posted')
       )
;

JeremyU SSChasing Mays Points: 640 More actions · Answer 8

It should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Acrual'

I was thinking smth like this, but there is a mistake somewhere it returns the whole dataset and doesn't filter anything.

SELECT *
  FROM final_output_2 fo
 WHERE EXISTS(SELECT 1
                FROM final_output_2 fo2
               WHERE fo2.[Voucher Num] = fo.[Voucher Num]
                 AND fo2.[G/L Transaction Type Desc] = 'GL Posted'
   OR fo2.[G/L Transaction Type Desc] IN ('GL Accrual Reversed', 'GL Accrual'))

JeremyU SSChasing Mays Points: 640 More actions · Answer 9

It should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Acrual'

I was thinking smth like this, but there is a mistake somewhere it returns the whole dataset and doesn't filter anything.

SELECT *
  FROM final_output_2 fo
 WHERE EXISTS(SELECT 1
                FROM final_output_2 fo2
               WHERE fo2.[Voucher Num] = fo.[Voucher Num]
                 AND fo2.[G/L Transaction Type Desc] = 'GL Posted'
   OR fo2.[G/L Transaction Type Desc] IN ('GL Accrual Reversed', 'GL Accrual'))

JeremyU SSChasing Mays Points: 640 More actions · Answer 10

It should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Acrual'

I was thinking smth like this, but there is a mistake somewhere it returns the whole dataset and doesn't filter anything.

SELECT *
  FROM final_output_2 fo
 WHERE EXISTS(SELECT 1
                FROM final_output_2 fo2
               WHERE fo2.[Voucher Num] = fo.[Voucher Num]
                 AND fo2.[G/L Transaction Type Desc] = 'GL Posted'
   OR fo2.[G/L Transaction Type Desc] IN ('GL Accrual Reversed', 'GL Accrual'))

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

The simplest would be to add a where clause - but I am guessing that you want all voucher's with the 'latest' transaction type available for that voucher.

If that is the case - then lookup ROW_NUMBER(). Set the partition based on the voucher number - and order by transaction type descending, then select where your row number = 1.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JeremyU SSChasing Mays Points: 640 More actions · Answer 12

Jeffrey not exactly. Query should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Accrual'. It is not based on latest transaction date.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 13

https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 14

JeremyU wrote:

Jeffrey not exactly. Query should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Accrual'. It is not based on latest transaction date.

Did you check my post?