SQL multiples select and group by using "first 1"

Question

Despite the confusing title, I think it's pretty simple I have a database in the following format: (EXAMPLE)

---------------------------------
| X101| X102      | X103 | X104 |
---------------------------------
| COD | PROD_NAME | TYPE | INFO | 

| 05  | PROD A1   | 05   | ABC  |
| 019 | TAPE 01   | 05   | ABC  |
| 052 | MASTER 0  | 03   | ABC  |
| 111 | APPLE     | 03   | ABC  |
| 150 | RANDOM0   | 05   | ABC  |
| 235 | ITEM661   | 03   | ABC  |
| 290 | ITEM874   | 03   | ABC  |
---------------------------------

I've tried in many ways, but I have not come up with a result. The database has a little more than 20 thousand products, I wanted to know how to 'select first 1' of each TYPE('group by TYPE' and show only the 'first 1' of each type)

Database is Firebird 1.5.

Answer 1

If You don't have any condition for "first 1" you can simply try.

SELECT * FROM yourTable GROUP BY TYPE

Answer 2

First one by what order, specifically?

WITH TYPES AS
(
  SELECT TYPE, COUNT(*) AS 'COUNT' FROM EXAMPLE GROUP BY TYPE
)
SELECT
  TYPE,
  (SELECT TOP 1 PROD_NAME FROM EXAMPLE e WHERE e.TYPE = t.TYPE ORDER BY COD ASC) AS 'FIRST', 
  COUNT
FROM TYPES t

This results in the following:

TYPE | FIRST    | COUNT
-----|----------|------
03   | MASTER 0 | 4
05   | TAPE 01  | 3

This what you're after?

Answer 3

One might use stored procedures.

CREATE PROCEDURE SelectFirstOfTypes() 
         RETURNS (COD INT, PROD_NAME VARCHAR(15), TYPE INT, INFO VARCHAR(15)) AS 
BEGIN 
  FOR SELECT DISTINCT TYPE FROM EXAMPLE INTO :TYPE DO BEGIN
    FOR SELECT FIRST 1 e.COD, e.PROD_NAME, e.INFO FROM EXAMPLE e
    WHERE :TYPE = e.TYPE  INTO :COD, :PROD_NAME, :INFO DO BEGIN
       SUSPEND; 
    END
  END 
END;

GRANT SELECT ON EXAMPLE TO SelectFirstOfTypes;

SELECT * FROM SelectFirstOfTypes();

Note, that both SELECT statements are wrapped in FOR-loop.

• outer one - because it can return multiple rows,
• inner one - because it can return zero rows for specific types

Some links

• http://www.ibexpert.net/ibe/index.php?n=Doc.WritingStoredProceduresAndTriggers
• http://www.destructor.de/firebird/storedproc.htm
• How to SELECT a PROCEDURE in Firebird 2.5
• http://www.pp4s.co.uk/main/tu-db-firebird-demo4.html