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.

@TimBiegeleisen Column 'Type', display only the first result with 05 and only the first result with 03, for example — ZaaPy, Jun 04 '17 at 15:02
Why are you still using Firebird 1.5?! The last release of 1.5 was 8 years ago, and since that time time a number of security bugs have been discovered and fixed, and a lot of new features have been added in recent versions that make it a lot easier to solve this in Firebird 3, than in Firebird 1.5. — Mark Rotteveel, Jun 04 '17 at 15:09
@MarkRotteveel I am already providing migration to fb3.0, which has a lot more features and is much more secure, but for now, unfortunately, it must be 1.5 — ZaaPy, Jun 04 '17 at 15:33
See if https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group?rq=1 fixes your problem. — Mark Rotteveel, Jun 04 '17 at 15:42
If you have found a solution to your problem, then please do not edit your question to add things like "solved" to the title and a solution to your question, instead post an **answer** with your solution, and - after the timeout - **accept** it. I have rolled back your last edit. — Mark Rotteveel, Jun 05 '17 at 07:37
that was not even a solution, work-around at best. The question was about Firebird, not about C# WPF — Arioch 'The, Jun 05 '17 at 08:48

score 0 · Answer 1 · answered Jun 04 '17 at 07:34

0

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?

answered Jun 04 '17 at 07:34

Ehryk

1,930
2
27
47

Exactly this result I need !!! But it did not work out here, 'TOP 1' does not recognize, maybe not for Firebird 1.5? – ZaaPy Jun 04 '17 at 15:24
@ZaaPy Firebird doesn't have `top`, the equivalent is [`first`](https://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-select.html#langrefupd25-first-skip), unfortunately CTE (common table expressions, ie `WITH`), was introduced in Firebird 2.1. – Mark Rotteveel Jun 04 '17 at 15:38

score 0 · Answer 2 · answered Jun 05 '17 at 09:14

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

score -2 · Answer 3 · answered Jun 04 '17 at 07:24

-2

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

SELECT * FROM yourTable GROUP BY TYPE

answered Jun 04 '17 at 07:24

Niral Patel

62
1
7

I've tried, but returns error: Invalid token. Dynamic SQL Error. SQL error code = -104. Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause). – ZaaPy Jun 04 '17 at 15:30
2

This might work in MySQL, it doesn't work like that in most other database systems, including Firebird. – Mark Rotteveel Jun 04 '17 at 15:40

SQL multiples select and group by using "first 1"

3 Answers3