Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
10
Erster Beitrag:
vor 4 Jahren, 5 Monaten
Letzter Beitrag:
vor 4 Jahren, 5 Monaten
Beteiligte Autoren:
Michael Drechsel, Peter Holemans, Paulo Oliveira, Bart VDE

[WD17] Query-Maximum problem

Startbeitrag von Michael Drechsel am 15.11.2013 09:37

Hi,

I have 2 files with

name, personal-id

and another with

personal-id, date, percent, description.

I need a query which select the maximum date and the rest of information of the second file so that I have finaly one record:

Name, date, percent, description

Any Idea ?

For the second file I user a query with maximum(), but this query can´t deliver the rest of the information like percent and description.

Antworten:

Hi Michael,

Maybe you could try it this way :

select f1.name, f2.date, f2.percent, f2.description
from f1
inner join f2 on (f1.personal-id = f2.personal-id)
where f2.date = (select max(date) from f2 where f2.personal-id = f1.personal-id)

Regards,
Bart

von Bart VDE - am 15.11.2013 10:44
Hi Michael,

Or:

select f1.name, max(f2.date), f2.percent, f2.description
from f1
inner join f2 on f1.personal-id = f2.personal-id
group by f1.name, f2.percent, f2.description

Cheers,

Peter H.

von Peter Holemans - am 15.11.2013 10:49
Hi,

doesn´t work all.

I forgot to say:

File 2 can have no, one or many records. In all cases I need one result record.

Maybe I am wrong:

The max() query statement need a grouping at the field which I need to sort. If I have more fields in the "group by" statement the max() doesn´t work correctly (The same as "distinct")) Thats my dilemma, I need the additional fields in my result table, but not in the query.

von Michael Drechsel - am 15.11.2013 16:46
If you need one record from the main_file with or without the second file just use left outer join instead of inner join
Sorry, i didn't ask before but it's for HF or non HF database?

Non HF normally this works:
Select f1.name, f2.date, f2.percent, f2.description
from main _file f1
left outer join second_file f2 on (f1.personal-id = f2.personal-id and
f2.date = (select max(fx.date) from second_file as fx where f2.personal-id = fx.personal-id)

For HF unfortunately sub selects are very limited (i can't find the syntax to work with sub selects in a proper manner), probably i will dot it with two queries:
query 1:
Select f1.name, f2.date, f2.percent, f2.description
from main _file f1
inner join second_file f2 on f1.personal-id = f2.personal-id
where f2.date = (select max(fx.date) from second_file as fx where f2.personal-id = fx.personal-id)
query 2:
Select f1.name
from main _file f1 where f1.personal-id not in (select f2.personal-id from second_file f2)

von Paulo Oliveira - am 15.11.2013 18:56
Thx, Paulo.

Yes, it´s HF SQL.

Why the hell is that so complicated ? It´s an basic task to find out things who are min/max with additional information about that.

von Michael Drechsel - am 15.11.2013 19:47
Hi Michael,

Piece of cake:

select f1.name, max(f2.date) as maxdate, f2.percent, f2.description
from f1
left outer join f2 on f1.personal-id = f2.personal-id
group by f1.name, f2.percent, f2.description
order by maxdate asc


Cheers,

Peter H.

von Peter Holemans - am 18.11.2013 10:13
Hi Peter,

doesn´t work with HF SQL.

MAX() needs always a "group by" statement where only the date is included.

Finaly I solved it with saving the GUID from the maxdata record of file2 in an extra field in F1. Not nice but works for me.

von Michael Drechsel - am 18.11.2013 10:35
Hi Michael,

I just read your original request and saw that I was too quick and that you needed another result set...

Bart VDE actually already pointed you in the right direction...

The problem with MAX() you mentionned is not an HFCS problem. Max(), Min(), Avg(), etc. always need a group-by statement unless you use it on a (result set with a) single field. This is just basic SQL standard...

So this should work:

select f1.name, f2.date, f2.percent, f2.description
from f1
left outer join f2 on f1.personal-id = f2.personal-id
where f2.date in (select max(date) from f2 aliasf2 where aliasf2.personal-id = f1.personal-id)
order by f2.date asc


Cheers,

Peter H.

von Peter Holemans - am 18.11.2013 11:53
Hi Peter,

it seems that this works. But very slow at HF SQL (yes I have indicies at all needed fields).

thx anyway

von Michael Drechsel - am 18.11.2013 12:21
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.