Firebird select from table distinct one field

Question

The question I asked yesterday was simplified but I realize that I have to report the whole story. I have to extract the data of 4 from 4 different tables into a Firebird 2.5 database and the following query works:

SELECT 
PRODUZIONE_T t.CODPRODUZIONE, 
PRODUZIONE_T.NUMEROCOMMESSA as numeroco,
    ANGCLIENTIFORNITORI.RAGIONESOCIALE1, 
    PRODUZIONE_T.DATACONSEGNA, 
    PRODUZIONE_T.REVISIONE,
    ANGUTENTI.NOMINATIVO, 
    ORDINI.T_DATA,
FROM PRODUZIONE_T
LEFT OUTER JOIN ORDINI_T ON PRODUZIONE_T.CODORDINE=ORDINI_T.CODORDINE 
INNER JOIN ANGCLIENTIFORNITORI ON ANGCLIENTIFORNITORI.CODCLIFOR=ORDINI_T.CODCLIFOR 
LEFT OUTER JOIN ANGUTENTI ON ANGUTENTI.IDUTENTE = PRODUZIONE_T.RESPONSABILEUC
ORDER BY right(numeroco,2) DESC, left(numeroco,3) desc 
rows 1 to 500;

However the query returns me double (or more) due to the REVISIONE column. How do I select only the rows of a single NUMEROCOMMESSA with the maximum REVISIONE value?

Answer 1

This should work:

select COD, ORDER, S.DATE, REVISION 
FROM TAB1
JOIN 
(
   select ORDER, MAX(REVISION) as REVISION
   FROM TAB1
   Group By ORDER
) m on m.ORDER = TAB1.ORDER and m.REVISION = TAB1.REVISION

Answer 2

Here you go - http://sqlfiddle.com/#!6/ce7cf/4

Sample Data (as u set it in your original question):

create table TAB1 (
  cod integer primary key,
  n_order varchar(10) not null,
  s_date date  not null,
  revision integer not null );

alter table tab1 add constraint UQ1 unique (n_order,revision);  

insert into TAB1 values ( 1, '001/18', '2018-02-01', 0 );
insert into TAB1 values ( 2, '002/18', '2018-01-31', 0 );
insert into TAB1 values ( 3, '002/18', '2018-01-30', 1 );

The query:

select * 
from tab1 d
join ( select n_ORDER, MAX(REVISION) as REVISION
       FROM TAB1
       Group By n_ORDER ) m
  on m.n_ORDER = d.n_ORDER and m.REVISION = d.REVISION

Suggestions:

1. Google and read the classic book: "Understanding SQL" by Martin Gruber
2. Read Firebird SQL reference: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25.html

Here is yet one more solution using Windowed Functions introduced in Firebird 3 - http://sqlfiddle.com/#!6/ce7cf/13

I do not have Firebird 3 at hand, so can not actually check if there would not be some sudden incompatibility, do it at home :-D

SELECT * FROM
(
   SELECT
     TAB1.*,
     ROW_NUMBER() OVER (
         PARTITION BY n_order 
         ORDER BY revision DESC
      ) AS rank
   FROM TAB1
) d
WHERE rank = 1

Read documentation

• https://community.modeanalytics.com/sql/tutorial/sql-window-functions/
• https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html

Which of the three (including Gordon's one) solution would be faster depends upon specific database - the real data, the existing indexes, the selectivity of indexes.

While window functions can make the join-less query, I am not sure it would be faster on real data, as it maybe can just ignore indexes on order+revision cortege and do the full-scan instead, before rank=1 condition applied. While the first solution would most probably use indexes to get maximums without actually reading every row in the table.

The Firebird-support mailing list suggested a way to break out of the loop, to only use a single query: The trick is using both windows functions and CTE (common table expression): http://sqlfiddle.com/#!18/ce7cf/2

WITH TMP AS (
  SELECT 
   *,
   MAX(revision) OVER (
         PARTITION BY n_order 
       ) as max_REV
  FROM TAB1
)
SELECT * FROM TMP
WHERE revision = max_REV

Answer 3

If you want the max revision number in Firebird:

select t.*
from tab1 t
where t.revision = (select max(t2.revision) from tab1 t2 where t2.order = t.order);

For performance, you want an index on tab1(order, revision). With such an index, performance should be competitive with any other approach.