Argthtjtr

I have a table in Firebird DB - see the

I need to know the up-to-date location information (LOCATION) of each device. Every device has unique serial number (SERIAL_NUMBER). The latest location is the row with highest all counter values (COUNTER1...COUNTER6).
I tried the following query, but it has to be complemented to get what I need. Unfortunately I don't have idea how to modify it.

SELECT LOCATION 

FROM TABLE 

WHERE SERIAL_NUMBER IN (SELECT DISTINCT SERIAL_NUMBER 

                        FROM TABLE) 

ORDER BY COUNTER1, COUNTER2, COUNTER3, COUNTER4, COUNTER5, COUNTER6

From the pseudo-answer, now deleted:

I need to find a row for every SERIAL_NUMBER with the greatest vaule
of COUNTER1 and if equal for COUNTER2 and if equal for
COUNTER3...COUNTER6. So finally it will be a set of rows for each
SERIAL_NUMBER with the latest location information. The Firebird ver.
is 2.5 The design is as it is and cannot be changed. There is no
timestamp column in the table to find a latest location information in
a straight way, so the greatest counters value represents the most
current location.

Here you have it: http://sqlfiddle.com/#!17/ff969/1

You would need to make obvious transitions to Firebird SQL flavor:

• Firebird can not insert many rows with one statement.


• Firebird uses select FIRST(1) ..... syntax instead of select .... LIMIT 1
  

Notice how I use one SELECT query after another, from simple queries building complex ones, testing every simple one (that it really returns what I need) before making mor complex constructions on top of them.

Read here more details about the process HOW to grow queries: https://stackoverflow.com/a/51398120/976391

PostgreSQL 9.6 Schema Setup:

create table tab(

  id /* INTeger NOT NULL AUTO_INCREMENT */ serial primary key,

  ser varchar(20) not null,

  Loc varchar(20) not null,

  c1 integer not null,

  c2 integer not null,

  c3 integer not null,

  c4 integer not null,

  c5 integer not null,

  c6 integer not null

 );



 insert into tab(ser,loc,c1,c2,c3,c4,c5,c6) values

 ('LSE4300308', 'Address 1', 0,0,0, 0,0,0),

 ('LHD4x22414', 'Address 2', 601,504,8, 130,0,0),

 ('LHD4x22414', 'Address 3', 2385,1163,54, 150,0,0),

 ('LSE4300308', 'Address 4', 2209,0,323, 0,0,0),

 ('LSE4300308', 'Address 5', 2257,0,330, 1661,926,0),

 ('LSE4300308', 'Address 6', 2278,0,330, 0,0,0),

 ('LSE4300308', 'Address 7', 2257,0,330, 1661,926,0),

 ('LSE4300308', 'Address 8', 2262,0,330, 1661,926,0),

 ('L873702373', 'Address 9', 12165,25467,1578, 2619,0,0),

 ('L873702373', 'Address A', 12165,25467,1578, 2619,0,0);



 create index t_ser on tab(ser);

 create /* descending */ index t_counters on tab(c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc);

Query 1:

Select * from tab

Results:

| id |        ser |       loc |    c1 |    c2 |   c3 |   c4 |  c5 | c6 |

|----|------------|-----------|-------|-------|------|------|-----|----|

|  1 | LSE4300308 | Address 1 |     0 |     0 |    0 |    0 |   0 |  0 |

|  2 | LHD4x22414 | Address 2 |   601 |   504 |    8 |  130 |   0 |  0 |

|  3 | LHD4x22414 | Address 3 |  2385 |  1163 |   54 |  150 |   0 |  0 |

|  4 | LSE4300308 | Address 4 |  2209 |     0 |  323 |    0 |   0 |  0 |

|  5 | LSE4300308 | Address 5 |  2257 |     0 |  330 | 1661 | 926 |  0 |

|  6 | LSE4300308 | Address 6 |  2278 |     0 |  330 |    0 |   0 |  0 |

|  7 | LSE4300308 | Address 7 |  2257 |     0 |  330 | 1661 | 926 |  0 |

|  8 | LSE4300308 | Address 8 |  2262 |     0 |  330 | 1661 | 926 |  0 |

|  9 | L873702373 | Address 9 | 12165 | 25467 | 1578 | 2619 |   0 |  0 |

| 10 | L873702373 | Address A | 12165 | 25467 | 1578 | 2619 |   0 |  0 |

Query 2:

Select distinct ser from tab

Results:

|        ser |

|------------|

| LSE4300308 |

| L873702373 |

| LHD4x22414 |

Query 3:

Select /* First(10) */ * from tab

  where ser = 'LSE4300308'

  order by c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc

  limit 10 

Results:

| id |        ser |       loc |   c1 | c2 |  c3 |   c4 |  c5 | c6 |

|----|------------|-----------|------|----|-----|------|-----|----|

|  6 | LSE4300308 | Address 6 | 2278 |  0 | 330 |    0 |   0 |  0 |

|  8 | LSE4300308 | Address 8 | 2262 |  0 | 330 | 1661 | 926 |  0 |

|  5 | LSE4300308 | Address 5 | 2257 |  0 | 330 | 1661 | 926 |  0 |

|  7 | LSE4300308 | Address 7 | 2257 |  0 | 330 | 1661 | 926 |  0 |

|  4 | LSE4300308 | Address 4 | 2209 |  0 | 323 |    0 |   0 |  0 |

|  1 | LSE4300308 | Address 1 |    0 |  0 |   0 |    0 |   0 |  0 |

Query 4:

Select /* First(1) */ * from tab

  where ser = 'LSE4300308'

  order by c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc

  limit 1 

Results:

| id |        ser |       loc |   c1 | c2 |  c3 | c4 | c5 | c6 |

|----|------------|-----------|------|----|-----|----|----|----|

|  6 | LSE4300308 | Address 6 | 2278 |  0 | 330 |  0 |  0 |  0 |

Query 5:

Select t1.ser, 

  ( Select /* First(1) */ loc from tab 

    Where ser = t1.ser

    order by c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc

    Limit 1)

From (Select distinct ser from tab) as t1

Results:

|        ser |       loc |

|------------|-----------|

| LSE4300308 | Address 6 |

| L873702373 | Address 9 |

| LHD4x22414 | Address 3 |

Firebird 2.1 conversion.
Note - unless this query is run very seldom, you STILL may be better amending the database structure. No matter what is already there, you still can ADD new helper tables and you still can add new triggers to keep those new tables up to date automatically. For example one single add-on table "most recent ID for every serial" would make your life simpler, executing update or insert command every time new row is inserted into locations.

create table so53371903(

  id integer primary key,

  ser varchar(20) not null,

  Loc varchar(20) not null,

  c1 integer not null,

  c2 integer not null,

  c3 integer not null,

  c4 integer not null,

  c5 integer not null,

  c6 integer not null

 );



 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (0,'LSE4300308', 'Address 1', 0,0,0, 0,0,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (1,'LHD4x22414', 'Address 2', 601,504,8, 130,0,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (2,'LHD4x22414', 'Address 3', 2385,1163,54, 150,0,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (3,'LSE4300308', 'Address 4', 2209,0,323, 0,0,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (4,'LSE4300308', 'Address 5', 2257,0,330, 1661,926,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (5,'LSE4300308', 'Address 6', 2278,0,330, 0,0,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (6,'LSE4300308', 'Address 7', 2257,0,330, 1661,926,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (7,'LSE4300308', 'Address 8', 2262,0,330, 1661,926,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (8,'L873702373', 'Address 9', 12165,25467,1578, 2619,0,0)

 ;

 insert into so53371903(id,ser,loc,c1,c2,c3,c4,c5,c6) values

   (9,'L873702373', 'Address A', 12165,25467,1578, 2619,0,0)

 ;



 create index t_ser on so53371903(ser);

 create descending index t_counters on so53371903(c1,c2,c3,c4,c5,c6);



Select * from so53371903;



Select distinct ser from so53371903;



Select first(10) * from so53371903

  where ser = 'LSE4300308'

  order by c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc;



Select first(1) * from so53371903

  where ser = 'LSE4300308'

  order by c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc;



Select t1.ser, 

  ( Select First(1) loc from so53371903

    Where ser = t1.ser

    order by c1 desc,c2 desc,c3 desc,c4 desc,c5 desc,c6 desc

   )

From (Select distinct ser from so53371903) as t1;

Note: Address 9 and Address 10 have the same values in counters, so do Address 5 and Address 7. Because there is no "maximum counters" between then - the "winning" row would be chosen at random. It may be that today there will be one winner, and running the same query month or year later would give different result in those rows.