Easier way?

[Check_Mail]

Helle,

 

in my case:

 

table agdatum

artnr, nr, typ, stueckg, datum

1        1        2        100    01.01.2022

1        1        0        0       01.01.2022

1        1        2        22     05.01.2022

1        2       2        33     05.01.2022

2       1        0        0       06.01.2022

 

I would group all artnr, nr where the typ > 0 after the last datum and get the price (stueckg) for this position

 

In case of artnr 1 and ag 1 is it stueckg 22 from 05.01.2022.

 

My sql seems to be to complicated or not perfect:

 

with cte as(

select artnr, nr, typ, max(datum) as dt from agdatum where typ > 0 group by artnr, nr, typ)

select artnr, nr, typ, (select stueckg from agdatum where artnr = cte.artnr and nr = cte.nr and typ = cte.typ and datum = cte.dt) from cte

 

is there a better way?

 

Thanks in advance.

 

Best regards

 

Olaf

[Mark Rotteveel]

On 04-11-2022 12:33, Check_Mail wrote:
[..]

> I would group all artnr, nr where the typ > 0 after the last datum and
> get the price (stueckg) for this position
>
> In case of artnr 1 and ag 1 is it stueckg 22 from 05.01.2022.
>
> My sql seems to be to complicated or not perfect:
>
> with cte as(
>
> select artnr, nr, typ, max(datum) as dt from agdatum where typ > 0 group
> by artnr, nr, typ)
>
> select artnr, nr, typ, (select stueckg from agdatum where artnr =
> cte.artnr and nr = cte.nr and typ = cte.typ and datum = cte.dt) from cte
>
> is there a better way?

Assuming you're using Firebird 3.0 or 4.0, you could use the
ROW_NUMBER() window function:

select artnr, nr, typ, stueckg
from (
select artnr, nr, typ, stueckg,
row_number() over(partition by artnr, nr order by datum desc) as rn

from agdatum
where typ > 0

)
where rn = 1

or - if you're using Firebird 4.0 or higher, a cross join lateral

with cte as(
select artnr, nr, typ, max(datum) as dt
from agdatum
where typ > 0
group by artnr, nr, typ
)

select a.artnr, a.nr, a.typ, a.stueckg
from cte
cross join lateral (
select artnr, nr, typ, stueckg

from agdatum
where artnr = cte.artnr and nr = cte.nr and typ = cte.typ and datum =
cte.dt

) a

or a - in any version - a normal join

with cte as(
select artnr, nr, typ, max(datum) as dt
from agdatum
where typ > 0
group by artnr, nr, typ
)

select a.artnr, a.nr, a.typ, a.stueckg
from cte
inner join agdatum a
on a.artnr = cte.artnr and a.nr = cte.nr and a.typ = cte.typ and
a.datum = cte.dt

dbfiddle: https://dbfiddle.uk/tvxBcvGH
--
Mark Rotteveel

[Steve Naidamast]

From my knowledge of SQL, using aggregates tends to increase query times, especially against large tables, while Joins, though very convenient, are quite heavy as well.

You may want to do an analysis of both sets of SQL if you rewrite your query.

I had a similar situation once where I had written three separate queries for a result.  My boss insisted I turn my 3 queries into a single query with Joins.  The result was that the latter query was slower than

my original 3 queries.

Of course, all databases have different strengths and weaknesses.  In this regard, my comments come from my experiences with SQL Server and Oracle.

Steve Naidamast

Sr. Software Engineer

[Check_Mail]

Thank you. 😊 Very interesting variants.

-----Ursprüngliche Nachricht-----
Von: firebird...@googlegroups.com <firebird...@googlegroups.com> Im Auftrag von Mark Rotteveel
Gesendet: Freitag, 4. November 2022 12:58
An: firebird...@googlegroups.com
Betreff: Re: [firebird-support] Easier way?

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/09bc3f48-4ceb-7d02-2756-301386bed17b%40lawinegevaar.nl.

[Svein Erling Tysvær]

Your solution seems OK, Olaf, but if there's only one record per day, then you could alternatively try

with cte as
( select a1.artnr, a1.nr, a1.typ, a1.stueckg
  from agdatum a1
  where a1.typ > 0
    and not exists( select *
                    from agdatum a2
                    where a1.artnr = a2.artnr
                      and a1.nr    = a2.nr
                      and a1.typ   = a2.typ
                      and a1.datum < a2.datum ) )
select d.artnr, d.nr, d.typ, c.stueckg
from agdatum d
left join cte c on d.artnr = c.artnr and d.nr = c.nr and d.typ = c.typ

It is also possible to write this without any cte:

select a1.artnr, a1.nr, a1.typ, a2.stueckg
from agdatum a1
left join a2 on a1.artnr = a2.artnr and a1.nr = a2.nr and a1.typ = a2.typ and a2.typ > 0
left join a3 on a2.artnr = a3.artnr and a2.nr = a3.nr and a2.typ = a3.typ and and a2.datum < a3.datum and a3.typ > 0
where a1.typ > 0
  and a3.typ is null

(I'm assuming typ is never null, if it can be null, then you need to change a3.typ is null to refer to another field that isn't nulll)

If there can be several records for each artnr, nr and typ on the same day you would need to do a little bit more, but you haven't told us which entry to choose in case of duplicates.

HTH,

Set

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/000001d8f041%24501c6bd0%24f0554370%24%40satron.de.

[Check_Mail]

Hello set,

 

first, thank you for your time. It is not my database design, I must import some data from another database, also firebird.

 

With your first cte I get more than the double amount of data, also typ 0 and typ null. With your second command I get a similar result as with my command. I'm amazed at how many options there are. Thank you so much, I think if there data still missing, they will be added manually.

 

 

Von: firebird...@googlegroups.com <firebird...@googlegroups.com> Im Auftrag von Svein Erling Tysvær
Gesendet: Freitag, 4. November 2022 13:21
An: firebird...@googlegroups.com
Betreff: Re: [firebird-support] Easier way?

 

Your solution seems OK, Olaf, but if there's only one record per day, then you could alternatively try

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAKC2LgyR6bmeA0egtn8haOAukPQXhZLQs6NZ_e21AJ4vS3A0mA%40mail.gmail.com.