the Max() function

[radek225]

I need to add tblProcesyZlecenia.id_procesy_zlecenia to "select" section into my query. But then query will return 3 rows instead of 1 row. So how can select tblZlecenia.id_zlecenia, tblProcesyZlecenia.id_procesy_zlecenia where tblProcesyZlecenia.kolejnosc is max value?

SELECT tblZlecenia.id_zlecenia, max(tblProcesyZlecenia.kolejnosc) as kolejnoscmax
from tblZlecenia inner join (tblKartaTechnologiczna inner join tblProcesyZlecenia on tblProcesyZlecenia.id_kartatechnologiczna = tblKartaTechnologiczna.id_kartatechnologiczna) on tblZlecenia.id_zlecenia = tblkartatechnologiczna.id_zlecenia
where nz(datarozpoczecia, 0) > 0 and tblZlecenia.id_zlecenia = "864/15"
group by tblZlecenia.id_zlecenia;

 

[Ranman256]

the more fields you add, the more records you get.
ex
A. Max([Qty]) gives 1 max record
B. [person], Max([Qty]) will give a Max for Every person. many records.

so you will need 2 queries, Q1 to get the max, then Q2 to match Q1 and pull the full record.

 

[radek225]

I thought so. THX

 

[arnelgp]

will Select Distinct, help you?

 

[radek225]

will Select Distinct, help you?

Unfortunately no:/

 

[arnelgp]

SELECT tblZlecenia.id_zlecenia, (Select max(tblProcesyZlecenia.kolejnosc) From tblProcesyZlecencia, tblKartaTecnologiczna Where tblKartaTechnologiczna.id_kartatechnologiczna = tblProcesyZlecenia.id_kartatechnologiczna And tblkartatechnologiczna.id_zlecenia = "864/15") as kolejnoscmax
From tblZlecenia where Nz(datarozpoczecia, 0) > 0 And tblZlecenia.id_zlecenia = "864/15"
group by tblZlecenia.id_zlecenia;

 

[radek225]

SELECT tblZlecenia.id_zlecenia, (Select max(tblProcesyZlecenia.kolejnosc) From tblProcesyZlecencia, tblKartaTecnologiczna Where tblKartaTechnologiczna.id_kartatechnologiczna = tblProcesyZlecenia.id_kartatechnologiczna And tblkartatechnologiczna.id_zlecenia = "864/15") as kolejnoscmax
From tblZlecenia where Nz(datarozpoczecia, 0) > 0 And tblZlecenia.id_zlecenia = "864/15"
group by tblZlecenia.id_zlecenia;

Interesting conception, but I need tblProcesyZlecenia.id_procesy_zlecenia to see also in my query. I should tell that datarozpoczecia = tblProcesyZlecenia.datarozpoczecia

 

[arnelgp]

please try, not tested.

SELECT tblZlecenia.id_zlecenia, (Select max(tblProcesyZlecenia.kolejnosc) From tblProcesyZlecencia, tblKartaTecnologiczna Where Nz(tblProcesyZlecenia.datarozpoczecia, 0) > 0 And tblKartaTechnologiczna.id_kartatechnologiczna = tblProcesyZlecenia.id_kartatechnologiczna And tblkartatechnologiczna.id_zlecenia = "864/15") as kolejnoscmax
From tblZlecenia where tblZlecenia.id_zlecenia = "864/15"
group by tblZlecenia.id_zlecenia;

 

[radek225]

please try, not tested.

SELECT tblZlecenia.id_zlecenia, (Select max(tblProcesyZlecenia.kolejnosc) From tblProcesyZlecencia, tblKartaTecnologiczna Where Nz(tblProcesyZlecenia.datarozpoczecia, 0) > 0 And tblKartaTechnologiczna.id_kartatechnologiczna = tblProcesyZlecenia.id_kartatechnologiczna And tblkartatechnologiczna.id_zlecenia = "864/15") as kolejnoscmax
From tblZlecenia where tblZlecenia.id_zlecenia = "864/15"
group by tblZlecenia.id_zlecenia;

Query still return only fields like "id_zlecenia" and "kolejnoscmax" but I also need "id_Procesy_zlecenia" - and that's the problem