0

I'm trying to filter out duplicates and just get the latest of the form name based on the the higher number, no dates on the table:

Form Name
abc
abc-01
abc-01
abc-02
abc-02
abc-03
def-01
def-02
xyz

And I would like to get this result:

Form Name
abc-03
def-02
xyz

What is the query to filter that out? Any suggestions? Thank you!

  • 3
    Which DBMS are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ... – a_horse_with_no_name Sep 29 '18 at 7:38
0

NOTE: Since OP hasn't designated the RDBMS I've opted for a SQL Server solution.

Assuming:

  • the numeric portion is always 2 digits
  • the numeric portion never starts with '00' [I'm going to use this as a default for names with no numeric extension]

Setup: (re-using @Glorfindel's code snippet)

CREATE TABLE forms (name nvarchar(50));

INSERT INTO forms VALUES
('abc'),
('abc-01'),
('abc-01'),
('abc-02'),
('abc-02'),
('abc-03'),
('def-01'),
('def-02'),
('xyz');

We start by breaking the names into 2 components, using a default '00' for names that don't have a numeric extension:

-- replace the '50' references with the max length of the 'name' column;
-- alternatively replace '50' with any number that's guaranteed to be longer than any 'name' values

select -- if name includes a '-' then strip off the first part, else use the entire name
       substring(name, 1, iif(charindex('-',name) > 0 , charindex('-',name) -1, 50))   as part1,

       -- if name includes a '-' then strip off the numeric component, else use '00' as a default
       iif(charindex('-',name) > 0, substring(name, charindex('-',name) +1, 50), '00') as part2

from   forms
order by 1,2;

part1  part2
=====  =====
abc    00      -- for 'abc' we default part2 = '00'
abc    01
abc    01
abc    02
abc    02
abc    03
def    01
def    02
xyz    00      -- for 'xyz' we default part2 = '00'

We'll convert this query to a Common Table Expression (CTE), and then use a basic max()/group by construct to find the desired rows ... remembering to ignore the default '00' values when displaying our final results:

NOTE: Alternatively we could use a derived table in place of the CTE.

with parts as
(
select substring(name, 1, iif(charindex('-',name) > 0 , charindex('-',name) -1, 50))   as part1,
       iif(charindex('-',name) > 0, substring(name, charindex('-',name) +1, 50), '00') as part2
from   forms
)

select  part1 +

        -- ignore '00' otherwise append a '-' and the numeric portion
        case when max(part2) = '00' then '' else '-' + max(part2) end as 'Form Name'

from parts
group by part1
order by 1;

Form Name
=========
abc-03
def-02
xyz

Here's a SQL Server fiddle for the above.

0

The query below will probably work; the subquery extracts the highest number after the - ('Version') and the outer query concatenates them back into the original format.

SELECT CASE Version IS NULL
           WHEN 1 THEN ID
           ELSE CONCAT(ID, '-', Version)
           END
  FROM
(SELECT
  CASE LOCATE('-', name)
           WHEN 0 THEN name
           ELSE SUBSTRING(name, 1, LOCATE('-', name) - 1)
           END AS 'ID',
  MAX(CASE LOCATE('-', name)
           WHEN 0 THEN NULL
           ELSE SUBSTRING(name, LOCATE('-', name) + 1)
           END) AS 'Version'
  FROM forms
    GROUP BY CASE LOCATE('-', name)
           WHEN 0 THEN name
           ELSE SUBSTRING(name, 1, LOCATE('-', name) - 1)
           END) AS subquery

Here is an online demo.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.