Select first(...)...Group BY... = useless?

M

Mark Burns

I have found something interesting, but aggrevating, and I'm open to
suggestions as to how to do this another way.

1: the data looks like (and is from a query):
FldA, FldB, FldC, FldD, FldE
1 , PAP, ProdIss, false, 0
2, PTX, Blah, false, 667
2, PTX, ProdIss, false, 0
3, PFL, ProdIss, false, 0
4, PFE, Blah, false, 667
<snip>
14, MIC, Blah, false, 667
14, MIC, Other, True, 667
14, MIC, Micro, false, 0
15, OPC, Other, True, 667
15, OPC, PW/CC, false, 0
<...>

The desired results:
FldA, FldB, FldC, FldD, FldE
1 , PAP, ProdIss, false, 0
2, PTX, Blah, false, 667
3, PFL, ProdIss, false, 0
4, PFE, Blah, false, 667
<snip sequential FldA #d records>
14, MIC, Other, True, 667
15, OPC, Other, True, 667
<...>

da rulez:
FldE is a userID, 0 = a system default value, able to be overridden by a
specific UserID (like 667 in this case) so think a fldE DESC sort idea
FldD is a boolean value, and if true it should supercede any/all other
values for that userID (only 1 True value per Userid is permitted = it
indicates a temporary assignment record). FldA is an ID value, and must be
unique in the target recordset
fldB and fldC are attribute fields for FldA (from other tables).

The efforts thus far:
Option 1: so it in a single query w/subquery (simplified for example
purposes):
SELECT Vtbl.FldA, First(Vtbl.FldB) as FldB, First(Vtbl.FldC) as FldC,
First(Vtbl.FldD) as FldD, First(Vtbl.FldE) as FldE FROM
(SELECT tblA.FldA, tblA.FldB, qryB.FldC, tblC.FldD, tblC.fldE
FROM <complex table join expression here with tblA, qryB, tblC>
WHERE ((tblC.fldQ=True) or (tblC.fldQ is null)) AND (tblC.fldE IN (0, 667))
ORDER BY tblA.FldA, NZ(tblC.fldE, 0) DESC, tblC.fldQ ASC) AS Vtbl
GROUP BY Vtbl.FldA
ORDER BY Vtbl.FldA;

Option 2: same approach, but separated into two queries:
[InnerQuery]:
SELECT tblA.FldA, tblA.FldB, qryB.FldC, tblC.FldD, tblC.fldE
FROM <complex table join expression here with tblA, qryB, tblC>
WHERE ((tblC.fldQ=True) or (tblC.fldQ is null)) AND (tblC.fldE IN (0, 667))
ORDER BY tblA.FldA, NZ(tblC.fldE, 0) DESC, tblC.fldQ ASC

[OuterQuery]:
SELECT Iq.FldA, First(Iq.fldB), First(Iq.fldC), First(Iq.fldD), First(Iq.fldE)
FROM InnerQuery as Iq
GROUP BY Iq.fldA
ORDER BY Iq.fldA;

Now, what's really cute here is that Option 1 and Option 2 both produce
similar BUT DIFFERENT results, and NEITHER is quite the desired results.
The two results are CONSISTENLY THE SAME for each option, making you think
it's doing something close to what you want (i.e. your desired results) BUT
IT'S NOT.

So, I'm surmising that GROUP BY and First()/Last() are pretty much useless
for me here (if not in a more general sense). (*Why won't First() honor the
record sorting of the previous sub-querys/steps??* THEN it could be a
_USEFUL_ tool!)
 
M

Michel Walsh

So, basically, for any fldA, if fldE<>0, keep the record, else, keep the
record with fldE=0 ?



SELECT a.fldA,
Nz(b.fldB, a.fldB),
Nz(b.fldC, a.fldC),
Nz(b.fldD, a.fldD),
Nz(a.fldE, b.fldE)

FROM (SELECT * FROM tableName WHERE fldE=0) As a
LEFT JOIN (SELECT * FROM tableName WHERE fldE<>0) As b
ON a.fldA=b.fldA



should do.



Hoping it may help,
Vanderghast, Access MVP


Mark Burns said:
I have found something interesting, but aggrevating, and I'm open to
suggestions as to how to do this another way.

1: the data looks like (and is from a query):
FldA, FldB, FldC, FldD, FldE
1 , PAP, ProdIss, false, 0
2, PTX, Blah, false, 667
2, PTX, ProdIss, false, 0
3, PFL, ProdIss, false, 0
4, PFE, Blah, false, 667
<snip>
14, MIC, Blah, false, 667
14, MIC, Other, True, 667
14, MIC, Micro, false, 0
15, OPC, Other, True, 667
15, OPC, PW/CC, false, 0
<...>

The desired results:
FldA, FldB, FldC, FldD, FldE
1 , PAP, ProdIss, false, 0
2, PTX, Blah, false, 667
3, PFL, ProdIss, false, 0
4, PFE, Blah, false, 667
<snip sequential FldA #d records>
14, MIC, Other, True, 667
15, OPC, Other, True, 667
<...>

da rulez:
FldE is a userID, 0 = a system default value, able to be overridden by a
specific UserID (like 667 in this case) so think a fldE DESC sort idea
FldD is a boolean value, and if true it should supercede any/all other
values for that userID (only 1 True value per Userid is permitted = it
indicates a temporary assignment record). FldA is an ID value, and must
be
unique in the target recordset
fldB and fldC are attribute fields for FldA (from other tables).

The efforts thus far:
Option 1: so it in a single query w/subquery (simplified for example
purposes):
SELECT Vtbl.FldA, First(Vtbl.FldB) as FldB, First(Vtbl.FldC) as FldC,
First(Vtbl.FldD) as FldD, First(Vtbl.FldE) as FldE FROM
(SELECT tblA.FldA, tblA.FldB, qryB.FldC, tblC.FldD, tblC.fldE
FROM <complex table join expression here with tblA, qryB, tblC>
WHERE ((tblC.fldQ=True) or (tblC.fldQ is null)) AND (tblC.fldE IN (0,
667))
ORDER BY tblA.FldA, NZ(tblC.fldE, 0) DESC, tblC.fldQ ASC) AS Vtbl
GROUP BY Vtbl.FldA
ORDER BY Vtbl.FldA;

Option 2: same approach, but separated into two queries:
[InnerQuery]:
SELECT tblA.FldA, tblA.FldB, qryB.FldC, tblC.FldD, tblC.fldE
FROM <complex table join expression here with tblA, qryB, tblC>
WHERE ((tblC.fldQ=True) or (tblC.fldQ is null)) AND (tblC.fldE IN (0,
667))
ORDER BY tblA.FldA, NZ(tblC.fldE, 0) DESC, tblC.fldQ ASC

[OuterQuery]:
SELECT Iq.FldA, First(Iq.fldB), First(Iq.fldC), First(Iq.fldD),
First(Iq.fldE)
FROM InnerQuery as Iq
GROUP BY Iq.fldA
ORDER BY Iq.fldA;

Now, what's really cute here is that Option 1 and Option 2 both produce
similar BUT DIFFERENT results, and NEITHER is quite the desired results.
The two results are CONSISTENLY THE SAME for each option, making you think
it's doing something close to what you want (i.e. your desired results)
BUT
IT'S NOT.

So, I'm surmising that GROUP BY and First()/Last() are pretty much useless
for me here (if not in a more general sense). (*Why won't First() honor
the
record sorting of the previous sub-querys/steps??* THEN it could be a
_USEFUL_ tool!)
Click to expand...
 
M

Mark Burns

Michel,

You missed the part about fldD...
The business logic is a bit more involved than that.
#1 tblC contains entries from multiple userIDs
Condition1 (tblC data):
fldE = <specific UserID#>, fldD = -1
Condition2 (tblC data):
fldE = <specific UserID#>, fldD = 0 (and not in Condition1 list)
Condition3 (tblC data):
fldE = 0, fldD = 0 (E=0, D= -1 illegal by definitions) and not in UNION of
Condition1 & Condition2 lists
Condition4 (tblA data):
fldA not in UNION list of Cond1 & Cond2 & Cond3
PLUS tblA data massaged to merge with tblC data for final UNION of results...

Sure seems like a long way to go to generate about 40 records from some
really simple conditions, doesn't it?
....if only First() was actually a worthwhile JET SQL function, this could
all be done so much more simply and quickly...

....but thanks for taking the time to reply anyway.
I'm still hoping that someone out there sees a better way than "the long
way" I just outlined.

Michel Walsh said:
So, basically, for any fldA, if fldE<>0, keep the record, else, keep the
record with fldE=0 ?



SELECT a.fldA,
Nz(b.fldB, a.fldB),
Nz(b.fldC, a.fldC),
Nz(b.fldD, a.fldD),
Nz(a.fldE, b.fldE)

FROM (SELECT * FROM tableName WHERE fldE=0) As a
LEFT JOIN (SELECT * FROM tableName WHERE fldE<>0) As b
ON a.fldA=b.fldA



should do.



Hoping it may help,
Vanderghast, Access MVP


Mark Burns said:
I have found something interesting, but aggrevating, and I'm open to
suggestions as to how to do this another way.

1: the data looks like (and is from a query):
FldA, FldB, FldC, FldD, FldE
1 , PAP, ProdIss, false, 0
2, PTX, Blah, false, 667
2, PTX, ProdIss, false, 0
3, PFL, ProdIss, false, 0
4, PFE, Blah, false, 667
<snip>
14, MIC, Blah, false, 667
14, MIC, Other, True, 667
14, MIC, Micro, false, 0
15, OPC, Other, True, 667
15, OPC, PW/CC, false, 0
<...>

The desired results:
FldA, FldB, FldC, FldD, FldE
1 , PAP, ProdIss, false, 0
2, PTX, Blah, false, 667
3, PFL, ProdIss, false, 0
4, PFE, Blah, false, 667
<snip sequential FldA #d records>
14, MIC, Other, True, 667
15, OPC, Other, True, 667
<...>

da rulez:
FldE is a userID, 0 = a system default value, able to be overridden by a
specific UserID (like 667 in this case) so think a fldE DESC sort idea
FldD is a boolean value, and if true it should supercede any/all other
values for that userID (only 1 True value per Userid is permitted = it
indicates a temporary assignment record). FldA is an ID value, and must
be
unique in the target recordset
fldB and fldC are attribute fields for FldA (from other tables).

The efforts thus far:
Option 1: so it in a single query w/subquery (simplified for example
purposes):
SELECT Vtbl.FldA, First(Vtbl.FldB) as FldB, First(Vtbl.FldC) as FldC,
First(Vtbl.FldD) as FldD, First(Vtbl.FldE) as FldE FROM
(SELECT tblA.FldA, tblA.FldB, qryB.FldC, tblC.FldD, tblC.fldE
FROM <complex table join expression here with tblA, qryB, tblC>
WHERE ((tblC.fldQ=True) or (tblC.fldQ is null)) AND (tblC.fldE IN (0,
667))
ORDER BY tblA.FldA, NZ(tblC.fldE, 0) DESC, tblC.fldQ ASC) AS Vtbl
GROUP BY Vtbl.FldA
ORDER BY Vtbl.FldA;

Option 2: same approach, but separated into two queries:
[InnerQuery]:
SELECT tblA.FldA, tblA.FldB, qryB.FldC, tblC.FldD, tblC.fldE
FROM <complex table join expression here with tblA, qryB, tblC>
WHERE ((tblC.fldQ=True) or (tblC.fldQ is null)) AND (tblC.fldE IN (0,
667))
ORDER BY tblA.FldA, NZ(tblC.fldE, 0) DESC, tblC.fldQ ASC

[OuterQuery]:
SELECT Iq.FldA, First(Iq.fldB), First(Iq.fldC), First(Iq.fldD),
First(Iq.fldE)
FROM InnerQuery as Iq
GROUP BY Iq.fldA
ORDER BY Iq.fldA;

Now, what's really cute here is that Option 1 and Option 2 both produce
similar BUT DIFFERENT results, and NEITHER is quite the desired results.
The two results are CONSISTENLY THE SAME for each option, making you think
it's doing something close to what you want (i.e. your desired results)
BUT
IT'S NOT.

So, I'm surmising that GROUP BY and First()/Last() are pretty much useless
for me here (if not in a more general sense). (*Why won't First() honor
the
record sorting of the previous sub-querys/steps??* THEN it could be a
_USEFUL_ tool!)
Click to expand...
Click to expand...
 
Top