[Solved] SQL: youngest and oldest with additional conditions and grouping

[Hypno]

I have a problem with constructing a SQL query that I can't handle. There is a table with children's data.
The table has fields:
id_parent - parent ID (integer)
child_name – child name (string) irrelevant field,
child_surn – child surname (string) irrelevant field,
ch_dtbr - child's date of birth (date)
... others fields

Of course, a parent can have many children.

I need to get the result from this table in the form:
id_parent, ch_dtbr1, ch_dtbr3

The logic is as follows. I only choose parents who have at least 3 children. Those with 0, 1 and 2 are discarded from the result. Then, for such a parent, I choose the 3 youngest children. The field "ch_dtbr1" is the date of birth of the youngest of the three, and "ch_dtbr3" is the date of birth of the oldest of the three.

I need this result to further determine whether on a given day the person was the parent of at least 3 children under the age of 18.

[Rafkus_pl]

I would first calculate the age of the children and then count how many children each parent has under 18.
P.S. HAWING command not working??

EDIT:
I added one more query giving result:

I need to get the result from this table in the form:
id_parent, ch_dtbr1, ch_dtbr3

[Hypno]

I can't first calculate the ages of the children, because these results will only be data for the next query that will be called with different dates. So only then will I know what day to calculate the age of the child. That's the problem.
And HAVING works, and yes, it works well.

I think I solved the problem, but I'm pretty sure there's a simpler way to do it. In my solution, I do it in several steps, i.e. in successive queries.
1. I choose a query of parents from at least 3 children.
2. I query the children for the parents from step 1, sort the date of birth in descending order and give a ranking.
3. In the next query, I combine the 2 queries from step 2, the first with rank 1 and the second with rank 3. For ranks 1 and 3, of course, I take the date of birth. I append parent id to this and thus I have a table with 3 fields, parent id, child 1 date of birth and child 3 date of birth.

Based on the table from step 3 and a specific date, I calculate how old children 1 and 3 are on this particular date. If they are <18, then only as the final interesting results can I assign a true value to some flag.

[Cazer]

Hey @Hypno,

Great job on coming up with a solution! One way to simplify your process is to use window functions.

For instance, you can use the ROW_NUMBER() function over partitioned data. You partition by parent id and order by child's date of birth in descending order. This will give you a row number for each child per parent.

Next, you filter out rows where row number is less than or equal to 3. This way, you're left with parents who have at least 3 children.

Finally, you pivot on the row number to get the birth dates of the first and third child.

Here's a rough outline:

Code: Select all

WITH children_rn AS (
  SELECT id_parent, ch_dtbr,
         ROW_NUMBER() OVER(PARTITION BY id_parent ORDER BY ch_dtbr DESC) as rn
  FROM your_table
),
filtered_children AS (
  SELECT * FROM children_rn
  WHERE rn <= 3
)
SELECT id_parent, MAX(CASE WHEN rn=1 THEN ch_dtbr END) as ch_dtbr1,
       MAX(CASE WHEN rn=3 THEN ch_dtbr END) as ch_dtbr3
FROM filtered_children
GROUP BY id_parent
HAVING COUNT(*) = 3

This approach should reduce the number of steps. Hope it helps!

[Hypno]

As soon as I master the use of the window function.... so far this is something new for me. In heavily extrinsic mode, I recall SQL. Things have changed a bit since Access 2000 and Informix '90

PS I am begging OpenOffice developers to remember the formatted content of the SQL query!! My queries have KB of text each and removing tabs and line breaks is harakiri. I start editing such a query by reformatting the content. Now for the most complex queries in the files on the side I save myself a formatted character. People are going to Mars and this is shit.

[Hagar Delest]

PS I am begging OpenOffice developers to remember the formatted content of the SQL query!!
[...]
People are going to Mars and this is shit.

You are barking the wrong tree. We are just users like you.
If you want to be heard by developers, write to their mailing list.
Else, try LibreOffice, it may do better (not sure at all for this topic but at least it is more actively developed than AOO).

[Villeroy]

It is plain text. Create a form document without form to store your SQL drafts or use some plain text file with *.sql extension. Any code editor should open text files with .sql extension with proper syntax highlighting.
Nobody is working on Base. Sometimes they fix a serious bug. This one is not serious.