[CLOSED] Sorting results without grouping/printing?

I have run into a situation a couple of times now where the FOCUS syntax seems to conflict with the query I'm trying to write. There is probably a trick to what I'm trying to do here, hence this post...

Say I want only the first record for every group of records that share a common identifier, but it has to be the first record in a certain order. If I'd just use FST there's no guarantee that the record I get back is the one I want. It's just the first record the database encountered. But if I'd add a BY-clause, then I would also get every record after that, even with NOPRINT.

For example, take:

TABLE FILE CAR
SUM
	FST.CAR
BY COUNTRY
END

How do I make sure I get the first car per country alphabetically? That should be:

 COUNTRY   | CAR 
-----------+------------
 ENGLAND   | JAGUAR 
 FRANCE    | PEUGEOT 
 ITALY     | ALFA ROMEO 
 JAPAN     | DATSUN 
 W GERMANY | AUDI

Incidentally that table seems to be sorted alphabetically already, so the results happen to come out in the right order by default. But how do I make that a certainty?

This message has been edited. Last edited by: Kerry,

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Alan B

Virtuoso

posted

Hide Post

The FST. and LST. only work on the CAR file as it is a FOCUS db where segment keys are COUNTRY and CAR.

Try:

TABLE FILE CAR
PRINT      COUNTRY 
           CAR 
           MODEL 
BY HIGHEST DCOST NOPRINT
ON TABLE HOLD
END

TABLE FILE HOLD
SUM
       FST.CAR
       MIN.CAR
       LST.CAR
       MAX.CAR
BY         COUNTRY
END

to see what can happen. I beleive that for SQL FST is translated to MIN and LST to MAX. But someone else may know better.

Alan.
WF 7.705/8.007

Posts: 1451 | Location: Portugal | Registered: February 07, 2007

posted

  
TABLE FILE CAR
  BY COUNTRY
  BY LOWEST 1 CAR
END

Please Wait. Your request is being processed...