Postgresql duplicating values

Question

Good afternoon, I need help to be able to "inactivate" a line, for my result these lines are duplicated, and I only need 1 of them, I have several examples like this, this is only 1 of the cases

    SELECT p.cd_key, r.nm_lane, to_char(r.dt_record_pass, 'yyyyMMdd HH24:MI:SS'),
    r.ds_license_plate, r.vl_speed FROM tbgen_rdo r, tbgen_processor p, tbgen_equipment e 
   WHERE p.id_processor = r.id_processor AND p.id_equipment = e.id_equipment 
   and r.dt_record_pass between '2021-09-27 10:13:00' and '2021-09-27 10:13:01' and 
   r.ds_license_plate ='KRR1000'

resulted:

cd_key	nm_lane	to_char	ds_license_plate	vl_speed
1516617158432	A	20210927 10:13:00	KRR1000	33
1516617158432	B	20210927 10:13:00	KRR1000	34

I consider this value doubled because I look only at the ds_license_plate and the date (to_char) vl_speed and nm_lane don't matter in this case, I only need to bring 1 record of this

I always consider the shortest lane, in this case lane A

Those rows aren't duplicates. Their nm_lane and vl_speed columns differ. — AdamKG
– AdamKG, Commented Jan 5, 2022 at 18:28
Welcome to the SO community. The community will help with your issues, but it has certain expectations of you. One of the first is do not post images, post formatted text instead. Also yo should post table descriptions (ddl) and sample data - also as text. Please spend a few minuets to take the Tour and review How to Ask. — Belayer
– Belayer, Commented Jan 5, 2022 at 18:39
Hi, could you please expand your expected result as well as add your tables schemas? — Stanislav Balia
– Stanislav Balia, Commented Jan 5, 2022 at 18:51
@AdamKG I consider this value doubled because I look only at the ds_license_plate and the date (to_char) vl_speed and nm_lane don't matter in this case, I only need to bring 1 record of this — Leonardo Silva
– Leonardo Silva, Commented Jan 5, 2022 at 19:28
That's fine, but which one do you want to keep? You can't just say to a database "give me 1 of them" when it thinks there are 2; you haven't to be explicit about what it is to do, for example it can give you the max speed or the avg speed, the min lane, the count of different lane numbers, the row with the max speed etc — Caius Jard
– Caius Jard, Commented Jan 5, 2022 at 19:48

Caius Jard · Accepted Answer · 2022-01-05 20:34:48Z

I always consider the shortest lane, in this case lane A

WITH x as (
 SELECT 
  p.cd_key, 
  r.nm_lane, 
  to_char(r.dt_record_pass, 'yyyyMMdd HH24:MI:SS') as dt,
  r.ds_license_plate, 
  r.vl_speed, 
  ROW_NUMBER() OVER(PARTITION BY to_char(r.dt_record_pass, 'yyyyMMdd HH24:MI:SS'), r.ds_license_plate ORDER BY r.nm_lane) rn 

FROM
  tbgen_rdo r
  INNER JOIN tbgen_processor p ON p.id_processor = r.id_processor 
  INNER JOIN tbgen_equipment e ON p.id_equipment = e.id_equipment 
WHERE  
   r.dt_record_pass between '2021-09-27 10:13:00' and '2021-09-27 10:13:01' and 
   r.ds_license_plate ='KRR1000'
)
SELECT * FROM X WHERE rn = 1

We ask the Database to number the rows according to increasing order of nm_lane, then we filter it down to just the first row (the row with rn = 1). The "partition by" means the row numbering will start over from 1 again if the date or the license plate changes (which it won't for this example because you've fixed it in the where clause, but just in case you search for multiple plates one day..) - this query would this pick the lowest lane row for every unique variation of license plate and date seen in the query

I've also updated the JOIN syntax to 1992 (that's a year) style - about 30 years ago improvements to the spec meant we didn't have to write joins like FROM t1, t2 WHERE t1.x = t2.y any more. It's recommended to write all your joins in this syntax

Note that you said "shortest" for lane and I interpreted that as "soonest in the alphabet", but if you mean there is some column somewhere that literally specifies the lane length in yards, miles etc you should use that column as your ORDER BY

Richard Wheeldon · Accepted Answer · 2022-01-06 11:48:46Z

0

Changing:

select p.cd_key, ...

to:

select distinct on (r.dt_record_pass r.ds_license_plate) p.cd_key, ...

may yield a cleaner query assuming you can add a sensible order-by clause.

See https://www.postgresql.org/docs/14/sql-select.html

"SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first."

answered Jan 6, 2022 at 11:48

Richard Wheeldon

1,14717 silver badges30 bronze badges

Collectives™ on Stack Overflow

Postgresql duplicating values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related