How to select the longest value of a string?

Question

HI

I'm writing a query that selects different barcodes. Every barcode has a specific sequence. But in the database the barcode exicsts multiple times. Everytime a sequence is added the barcode is visable in a different row. For example:

![Enter image description here](/media/postImages/original/IM3zpCRGaFRVSZq98cXYUe2g)

I don't want to use a date filter because sometimes two sequences happen at the same date. Now I've used three select filters but I'm wondering if it's possible to use an other way.

SELECT 
  wc.dn_barcode
, (
    SELECT da_waarnemingsequence
                FROM collo_dwh.collo wc2
                WHERE wc2.dn_barcode = wc.dn_barcode
                AND  length(sequence) = 
                (
                    SELECT max(length(sequence) 
                    FROM collo_dwh.collo wc3
                    WHERE wc3.dn_barcode = wc2.dn_barcode
                    )
)  sequence

FROM
  (database wc)

WHERE 
wc.da_datum_sortering1 BETWEEN "date_add"('day', -7, current_date) AND current_date 
AND wc.da_landcode_gea = 'NL'
AND wc.sequence LIKE '%A1%B1%'
AND wc.sequenceNOT LIKE '%I%'

Accepted Answer

Here's an alternative using a window function:

```
SELECT barcode, sequence, d FROM (
     SELECT barcode, sequence, d, rank() OVER (PARTITION BY barcode
                             ORDER BY length(sequence) desc) AS r
     FROM "db1"."barcodes" 
) as windw
where windw.r = 1

```

How to select the longest value of a string?

相關內容