Access keyword "First" = SQL Top?

  • Thread starter Thread starter Adamus Turner
  • Start date Start date
A

Adamus Turner

Hi All,

I'm hoping someone can explain this but first I'll give a little history. I
was recently hired for a job and one task is to convert all Access queries
into SQL. I'm in the process of converting the following query and for the
life of me can't figure out what this guy was trying to do. Any advice on how
to convert this to SQL would be utterly appreaciated: What are all these
"firsts" suppose to accomplish?

INSERT INTO tbl_All_Orders_Temp ( Company, Item, PurchOrd, DMOrdType,
HdrBuyFromBP, HdrBuyFromBPName, OrdType, OrdTypeDesc, HdrOrdDate, DelDate,
HdrWhse, HdrWhseDesc, Buyer, BuyerName, TermOfDel, TermOfDelDesc, CarrierCd,
CarrierCdDesc, HdrConfDelDate, FinSuppGrp, FinSuppGrpDesc, [Position], Seq,
BuyFromBP, BuyFromBPName, ShipFromBP, ShipFromBPName, PurchPriceUnit,
ConvFactorPriceInv, ItemGrp, ItemGrpDesc, StatGrp, StatGrpDesc, OrdDateLine,
PlanDelDate, CurrPlanDelDate, ConfDelDate, ChngDelDate, OrdQty, PurchUnit,
ConverFactorPriceInv, QtyDel, RejQty, BackOrdQty, TaxCode, Whse, WhseDesc,
TotalApprQty, CntrNumb, CntrPosition, CostCompon, CostComponDesc, Insp,
ActDelDate, OrdConfDate, ReceiptNumb, PackSlipNumb, PackSlipQty, Canc,
ShipDate, DelAmt, PriceMatrixDef, ChngReasLines, ChngReasLinesDesc,
ChngReasTyp, ChngReasTypDesc, Print, HdrOrdDateTime, OrdDateLineTime,
ConfDelDateTime, ChngDelDateTime, WhseAddr, WhseAddrName, Name, [STOCKOUT IND]
, [Boeing Follow-Up], Date_Added, Proj_Combo, Order_Combo, Orginating_Report )

SELECT tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd, First
(tbl_RID_TEMP.DMOrdType) AS FirstOfDMOrdType, First(tbl_RID_TEMP.HdrBuyFromBP)
AS FirstOfHdrBuyFromBP, First(tbl_RID_TEMP.HdrBuyFromBPName) AS
FirstOfHdrBuyFromBPName, First(tbl_RID_TEMP.OrdType) AS FirstOfOrdType, First
(tbl_RID_TEMP.OrdTypeDesc) AS FirstOfOrdTypeDesc, First(tbl_RID_TEMP.
HdrOrdDate) AS FirstOfHdrOrdDate, First(tbl_RID_TEMP.DelDate) AS
FirstOfDelDate, First(tbl_RID_TEMP.HdrWhse) AS FirstOfHdrWhse, First
(tbl_RID_TEMP.HdrWhseDesc) AS FirstOfHdrWhseDesc, First(tbl_RID_TEMP.Buyer)
AS FirstOfBuyer, First(tbl_RID_TEMP.BuyerName) AS FirstOfBuyerName, First
(tbl_RID_TEMP.TermOfDel) AS FirstOfTermOfDel, First(tbl_RID_TEMP.
TermOfDelDesc) AS FirstOfTermOfDelDesc, First(tbl_RID_TEMP.CarrierCd) AS
FirstOfCarrierCd, First(tbl_RID_TEMP.CarrierCdDesc) AS FirstOfCarrierCdDesc,
First(tbl_RID_TEMP.HdrConfDelDate) AS FirstOfHdrConfDelDate, First
(tbl_RID_TEMP.FinSuppGrp) AS FirstOfFinSuppGrp, First(tbl_RID_TEMP.
FinSuppGrpDesc) AS FirstOfFinSuppGrpDesc, tbl_RID_TEMP.Position, Max
(tbl_RID_TEMP.Seq) AS MaxOfSeq, tbl_RID_TEMP.BuyFromBP, First(tbl_RID_TEMP.
BuyFromBPName) AS FirstOfBuyFromBPName, First(tbl_RID_TEMP.ShipFromBP) AS
FirstOfShipFromBP, First(tbl_RID_TEMP.ShipFromBPName) AS
FirstOfShipFromBPName, First(tbl_RID_TEMP.PurchPriceUnit) AS
FirstOfPurchPriceUnit, First(tbl_RID_TEMP.ConvFactorPriceInv) AS
FirstOfConvFactorPriceInv, First(tbl_RID_TEMP.ItemGrp) AS FirstOfItemGrp,
First(tbl_RID_TEMP.ItemGrpDesc) AS FirstOfItemGrpDesc, First(tbl_RID_TEMP.
StatGrp) AS FirstOfStatGrp, First(tbl_RID_TEMP.StatGrpDesc) AS
FirstOfStatGrpDesc, First(tbl_RID_TEMP.OrdDateLine) AS FirstOfOrdDateLine,
First(tbl_RID_TEMP.PlanDelDate) AS FirstOfPlanDelDate, First(tbl_RID_TEMP.
CurrPlanDelDate) AS FirstOfCurrPlanDelDate, First(tbl_RID_TEMP.ConfDelDate)
AS FirstOfConfDelDate, First(tbl_RID_TEMP.ChngDelDate) AS FirstOfChngDelDate,
Max(tbl_RID_TEMP.OrdQty) AS MaxOfOrdQty, First(tbl_RID_TEMP.PurchUnit) AS
FirstOfPurchUnit, First(tbl_RID_TEMP.ConverFactorPriceInv) AS
FirstOfConverFactorPriceInv, Sum(tbl_RID_TEMP.QtyDel) AS SumOfQtyDel, First
(tbl_RID_TEMP.RejQty) AS FirstOfRejQty, First(tbl_RID_TEMP.BackOrdQty) AS
FirstOfBackOrdQty, First(tbl_RID_TEMP.TaxCode) AS FirstOfTaxCode, First
(tbl_RID_TEMP.Whse) AS FirstOfWhse, First(tbl_RID_TEMP.WhseDesc) AS
FirstOfWhseDesc, First(tbl_RID_TEMP.TotalApprQty) AS FirstOfTotalApprQty,
First(tbl_RID_TEMP.CntrNumb) AS FirstOfCntrNumb, First(tbl_RID_TEMP.
CntrPosition) AS FirstOfCntrPosition, First(tbl_RID_TEMP.CostCompon) AS
FirstOfCostCompon, First(tbl_RID_TEMP.CostComponDesc) AS
FirstOfCostComponDesc, First(tbl_RID_TEMP.Insp) AS FirstOfInsp, Max
(tbl_RID_TEMP.ActDelDate) AS MaxOfActDelDate, First(tbl_RID_TEMP.OrdConfDate)
AS FirstOfOrdConfDate, First(tbl_RID_TEMP.ReceiptNumb) AS FirstOfReceiptNumb,
First(tbl_RID_TEMP.PackSlipNumb) AS FirstOfPackSlipNumb, First(tbl_RID_TEMP.
PackSlipQty) AS FirstOfPackSlipQty, First(tbl_RID_TEMP.Canc) AS FirstOfCanc,
First(tbl_RID_TEMP.ShipDate) AS FirstOfShipDate, First(tbl_RID_TEMP.DelAmt)
AS FirstOfDelAmt, First(tbl_RID_TEMP.PriceMatrixDef) AS FirstOfPriceMatrixDef,
First(tbl_RID_TEMP.ChngReasLines) AS FirstOfChngReasLines, First(tbl_RID_TEMP.
ChngReasLinesDesc) AS FirstOfChngReasLinesDesc, First(tbl_RID_TEMP.
ChngReasTyp) AS FirstOfChngReasTyp, First(tbl_RID_TEMP.ChngReasTypDesc) AS
FirstOfChngReasTypDesc, First(tbl_RID_TEMP.Print) AS FirstOfPrint, First
(tbl_RID_TEMP.HdrOrdDateTime) AS FirstOfHdrOrdDateTime, First(tbl_RID_TEMP.
OrdDateLineTime) AS FirstOfOrdDateLineTime, First(tbl_RID_TEMP.
ConfDelDateTime) AS FirstOfConfDelDateTime, First(tbl_RID_TEMP.
ChngDelDateTime) AS FirstOfChngDelDateTime, First(tbl_RID_TEMP.WhseAddr) AS
FirstOfWhseAddr, First(tbl_RID_TEMP.WhseAddrName) AS FirstOfWhseAddrName,
First(tbl_RID_TEMP.Name) AS FirstOfName, First(tbl_RID_TEMP.[STOCKOUT IND])
AS [FirstOfSTOCKOUT IND], First(tbl_RID_TEMP.[Boeing Follow-Up]) AS
[FirstOfBoeing Follow-Up], Max(tbl_RID_TEMP.Date_Added) AS MaxOfDate_Added,
tbl_RID_TEMP.Proj_Combo, tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.
Orginating_Report
FROM tbl_RID_TEMP
GROUP BY tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd,
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
 
'First' was originally intended to refer to the first value in a
sorted list, just as Max gives the maximum value in a set.

It also happens to be faster than Min or Max, because it
doesn't have to look at or compare any records other than
the first one it finds.

Unfortunately, in Access 'First' is not reliable when used
with complex queries, and when used with an unsorted
query is just reliable enough to be dangerous.

So by looking at the query I can't tell if the designer was
just using 'first' to get a fast value from a poorly normalised
data source, or was just lucky that the unsupported behaviour
implied by the name 'first' gave him the results he wanted.

You will have to look at the data to see if he was just trying
to get a fast value from poorly normalised data - in which
case you can use a fast procedure that returns an unsorted
record - or if he really did think he was getting the first record
from a sorted set - in which case you will have to use a
slow procedure that returns a record from a sorted set.

(david)



returns unexpected results
if you don't watch what you are doing
Adamus Turner said:
Hi All,

I'm hoping someone can explain this but first I'll give a little history. I
was recently hired for a job and one task is to convert all Access queries
into SQL. I'm in the process of converting the following query and for the
life of me can't figure out what this guy was trying to do. Any advice on how
to convert this to SQL would be utterly appreaciated: What are all these
"firsts" suppose to accomplish?

INSERT INTO tbl_All_Orders_Temp ( Company, Item, PurchOrd, DMOrdType,
HdrBuyFromBP, HdrBuyFromBPName, OrdType, OrdTypeDesc, HdrOrdDate, DelDate,
HdrWhse, HdrWhseDesc, Buyer, BuyerName, TermOfDel, TermOfDelDesc, CarrierCd,
CarrierCdDesc, HdrConfDelDate, FinSuppGrp, FinSuppGrpDesc, [Position], Seq,
BuyFromBP, BuyFromBPName, ShipFromBP, ShipFromBPName, PurchPriceUnit,
ConvFactorPriceInv, ItemGrp, ItemGrpDesc, StatGrp, StatGrpDesc, OrdDateLine,
PlanDelDate, CurrPlanDelDate, ConfDelDate, ChngDelDate, OrdQty, PurchUnit,
ConverFactorPriceInv, QtyDel, RejQty, BackOrdQty, TaxCode, Whse, WhseDesc,
TotalApprQty, CntrNumb, CntrPosition, CostCompon, CostComponDesc, Insp,
ActDelDate, OrdConfDate, ReceiptNumb, PackSlipNumb, PackSlipQty, Canc,
ShipDate, DelAmt, PriceMatrixDef, ChngReasLines, ChngReasLinesDesc,
ChngReasTyp, ChngReasTypDesc, Print, HdrOrdDateTime, OrdDateLineTime,
ConfDelDateTime, ChngDelDateTime, WhseAddr, WhseAddrName, Name, [STOCKOUT IND]
, [Boeing Follow-Up], Date_Added, Proj_Combo, Order_Combo, Orginating_Report )

SELECT tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd, First
(tbl_RID_TEMP.DMOrdType) AS FirstOfDMOrdType, First(tbl_RID_TEMP.HdrBuyFromBP)
AS FirstOfHdrBuyFromBP, First(tbl_RID_TEMP.HdrBuyFromBPName) AS
FirstOfHdrBuyFromBPName, First(tbl_RID_TEMP.OrdType) AS FirstOfOrdType, First
(tbl_RID_TEMP.OrdTypeDesc) AS FirstOfOrdTypeDesc, First(tbl_RID_TEMP.
HdrOrdDate) AS FirstOfHdrOrdDate, First(tbl_RID_TEMP.DelDate) AS
FirstOfDelDate, First(tbl_RID_TEMP.HdrWhse) AS FirstOfHdrWhse, First
(tbl_RID_TEMP.HdrWhseDesc) AS FirstOfHdrWhseDesc, First(tbl_RID_TEMP.Buyer)
AS FirstOfBuyer, First(tbl_RID_TEMP.BuyerName) AS FirstOfBuyerName, First
(tbl_RID_TEMP.TermOfDel) AS FirstOfTermOfDel, First(tbl_RID_TEMP.
TermOfDelDesc) AS FirstOfTermOfDelDesc, First(tbl_RID_TEMP.CarrierCd) AS
FirstOfCarrierCd, First(tbl_RID_TEMP.CarrierCdDesc) AS FirstOfCarrierCdDesc,
First(tbl_RID_TEMP.HdrConfDelDate) AS FirstOfHdrConfDelDate, First
(tbl_RID_TEMP.FinSuppGrp) AS FirstOfFinSuppGrp, First(tbl_RID_TEMP.
FinSuppGrpDesc) AS FirstOfFinSuppGrpDesc, tbl_RID_TEMP.Position, Max
(tbl_RID_TEMP.Seq) AS MaxOfSeq, tbl_RID_TEMP.BuyFromBP, First(tbl_RID_TEMP.
BuyFromBPName) AS FirstOfBuyFromBPName, First(tbl_RID_TEMP.ShipFromBP) AS
FirstOfShipFromBP, First(tbl_RID_TEMP.ShipFromBPName) AS
FirstOfShipFromBPName, First(tbl_RID_TEMP.PurchPriceUnit) AS
FirstOfPurchPriceUnit, First(tbl_RID_TEMP.ConvFactorPriceInv) AS
FirstOfConvFactorPriceInv, First(tbl_RID_TEMP.ItemGrp) AS FirstOfItemGrp,
First(tbl_RID_TEMP.ItemGrpDesc) AS FirstOfItemGrpDesc, First(tbl_RID_TEMP.
StatGrp) AS FirstOfStatGrp, First(tbl_RID_TEMP.StatGrpDesc) AS
FirstOfStatGrpDesc, First(tbl_RID_TEMP.OrdDateLine) AS FirstOfOrdDateLine,
First(tbl_RID_TEMP.PlanDelDate) AS FirstOfPlanDelDate, First(tbl_RID_TEMP.
CurrPlanDelDate) AS FirstOfCurrPlanDelDate, First(tbl_RID_TEMP.ConfDelDate)
AS FirstOfConfDelDate, First(tbl_RID_TEMP.ChngDelDate) AS FirstOfChngDelDate,
Max(tbl_RID_TEMP.OrdQty) AS MaxOfOrdQty, First(tbl_RID_TEMP.PurchUnit) AS
FirstOfPurchUnit, First(tbl_RID_TEMP.ConverFactorPriceInv) AS
FirstOfConverFactorPriceInv, Sum(tbl_RID_TEMP.QtyDel) AS SumOfQtyDel, First
(tbl_RID_TEMP.RejQty) AS FirstOfRejQty, First(tbl_RID_TEMP.BackOrdQty) AS
FirstOfBackOrdQty, First(tbl_RID_TEMP.TaxCode) AS FirstOfTaxCode, First
(tbl_RID_TEMP.Whse) AS FirstOfWhse, First(tbl_RID_TEMP.WhseDesc) AS
FirstOfWhseDesc, First(tbl_RID_TEMP.TotalApprQty) AS FirstOfTotalApprQty,
First(tbl_RID_TEMP.CntrNumb) AS FirstOfCntrNumb, First(tbl_RID_TEMP.
CntrPosition) AS FirstOfCntrPosition, First(tbl_RID_TEMP.CostCompon) AS
FirstOfCostCompon, First(tbl_RID_TEMP.CostComponDesc) AS
FirstOfCostComponDesc, First(tbl_RID_TEMP.Insp) AS FirstOfInsp, Max
(tbl_RID_TEMP.ActDelDate) AS MaxOfActDelDate, First(tbl_RID_TEMP.OrdConfDate)
AS FirstOfOrdConfDate, First(tbl_RID_TEMP.ReceiptNumb) AS FirstOfReceiptNumb,
First(tbl_RID_TEMP.PackSlipNumb) AS FirstOfPackSlipNumb, First(tbl_RID_TEMP.
PackSlipQty) AS FirstOfPackSlipQty, First(tbl_RID_TEMP.Canc) AS FirstOfCanc,
First(tbl_RID_TEMP.ShipDate) AS FirstOfShipDate, First(tbl_RID_TEMP.DelAmt)
AS FirstOfDelAmt, First(tbl_RID_TEMP.PriceMatrixDef) AS FirstOfPriceMatrixDef,
First(tbl_RID_TEMP.ChngReasLines) AS FirstOfChngReasLines, First(tbl_RID_TEMP.
ChngReasLinesDesc) AS FirstOfChngReasLinesDesc, First(tbl_RID_TEMP.
ChngReasTyp) AS FirstOfChngReasTyp, First(tbl_RID_TEMP.ChngReasTypDesc) AS
FirstOfChngReasTypDesc, First(tbl_RID_TEMP.Print) AS FirstOfPrint, First
(tbl_RID_TEMP.HdrOrdDateTime) AS FirstOfHdrOrdDateTime, First(tbl_RID_TEMP.
OrdDateLineTime) AS FirstOfOrdDateLineTime, First(tbl_RID_TEMP.
ConfDelDateTime) AS FirstOfConfDelDateTime, First(tbl_RID_TEMP.
ChngDelDateTime) AS FirstOfChngDelDateTime, First(tbl_RID_TEMP.WhseAddr) AS
FirstOfWhseAddr, First(tbl_RID_TEMP.WhseAddrName) AS FirstOfWhseAddrName,
First(tbl_RID_TEMP.Name) AS FirstOfName, First(tbl_RID_TEMP.[STOCKOUT IND])
AS [FirstOfSTOCKOUT IND], First(tbl_RID_TEMP.[Boeing Follow-Up]) AS
[FirstOfBoeing Follow-Up], Max(tbl_RID_TEMP.Date_Added) AS MaxOfDate_Added,
tbl_RID_TEMP.Proj_Combo, tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.
Orginating_Report
FROM tbl_RID_TEMP
GROUP BY tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd,
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
Click to expand...
 
The First aggregate function often is used as a way to avoid having to put
every single field into the GROUP BY clause in a totals query. If the value
in a field is essentially independent of the records (e.g., it's a company
name that is provided from a SELECT query that is combining data from
multiple tables), then using the First function means you don't need to put
that field in the GROUP BY clause at all. That keeps the GROUP BY clause
shorter and more manageable.

It also allows you to include a memo field in a totals query because using
First on a memo field does not truncate the field's data to 255 characters,
while putting the memo field in the GROUP BY clause will cause the field's
data to be truncated to 255 characters.

However, many people who are less experienced think that First function will
select the earliest record's value for that field (and similarly think that
Last function selects the latest record's value). This is incorrect. First
simply ignores the records and just selects the value from the first record
that the query "gets" that matches the query's setup requirements.
(Similarly, Last selects the value from the last record that the query
"gets".) The only way to get the value from the earliest record is to use an
ORDER BY clause that orders the data in ascending order according to the
field of interest, and then use the Min function. (Similarly, to get the
latest record's value, sort the data in ascending order and use the Max
function.)
--

Ken Snell
<MS ACCESS MVP>




Adamus Turner said:
Hi All,

I'm hoping someone can explain this but first I'll give a little history.
I
was recently hired for a job and one task is to convert all Access queries
into SQL. I'm in the process of converting the following query and for the
life of me can't figure out what this guy was trying to do. Any advice on
how
to convert this to SQL would be utterly appreaciated: What are all these
"firsts" suppose to accomplish?

INSERT INTO tbl_All_Orders_Temp ( Company, Item, PurchOrd, DMOrdType,
HdrBuyFromBP, HdrBuyFromBPName, OrdType, OrdTypeDesc, HdrOrdDate, DelDate,
HdrWhse, HdrWhseDesc, Buyer, BuyerName, TermOfDel, TermOfDelDesc,
CarrierCd,
CarrierCdDesc, HdrConfDelDate, FinSuppGrp, FinSuppGrpDesc, [Position],
Seq,
BuyFromBP, BuyFromBPName, ShipFromBP, ShipFromBPName, PurchPriceUnit,
ConvFactorPriceInv, ItemGrp, ItemGrpDesc, StatGrp, StatGrpDesc,
OrdDateLine,
PlanDelDate, CurrPlanDelDate, ConfDelDate, ChngDelDate, OrdQty, PurchUnit,
ConverFactorPriceInv, QtyDel, RejQty, BackOrdQty, TaxCode, Whse, WhseDesc,
TotalApprQty, CntrNumb, CntrPosition, CostCompon, CostComponDesc, Insp,
ActDelDate, OrdConfDate, ReceiptNumb, PackSlipNumb, PackSlipQty, Canc,
ShipDate, DelAmt, PriceMatrixDef, ChngReasLines, ChngReasLinesDesc,
ChngReasTyp, ChngReasTypDesc, Print, HdrOrdDateTime, OrdDateLineTime,
ConfDelDateTime, ChngDelDateTime, WhseAddr, WhseAddrName, Name, [STOCKOUT
IND]
, [Boeing Follow-Up], Date_Added, Proj_Combo, Order_Combo,
Orginating_Report )

SELECT tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd,
First
(tbl_RID_TEMP.DMOrdType) AS FirstOfDMOrdType,
First(tbl_RID_TEMP.HdrBuyFromBP)
AS FirstOfHdrBuyFromBP, First(tbl_RID_TEMP.HdrBuyFromBPName) AS
FirstOfHdrBuyFromBPName, First(tbl_RID_TEMP.OrdType) AS FirstOfOrdType,
First
(tbl_RID_TEMP.OrdTypeDesc) AS FirstOfOrdTypeDesc, First(tbl_RID_TEMP.
HdrOrdDate) AS FirstOfHdrOrdDate, First(tbl_RID_TEMP.DelDate) AS
FirstOfDelDate, First(tbl_RID_TEMP.HdrWhse) AS FirstOfHdrWhse, First
(tbl_RID_TEMP.HdrWhseDesc) AS FirstOfHdrWhseDesc,
First(tbl_RID_TEMP.Buyer)
AS FirstOfBuyer, First(tbl_RID_TEMP.BuyerName) AS FirstOfBuyerName, First
(tbl_RID_TEMP.TermOfDel) AS FirstOfTermOfDel, First(tbl_RID_TEMP.
TermOfDelDesc) AS FirstOfTermOfDelDesc, First(tbl_RID_TEMP.CarrierCd) AS
FirstOfCarrierCd, First(tbl_RID_TEMP.CarrierCdDesc) AS
FirstOfCarrierCdDesc,
First(tbl_RID_TEMP.HdrConfDelDate) AS FirstOfHdrConfDelDate, First
(tbl_RID_TEMP.FinSuppGrp) AS FirstOfFinSuppGrp, First(tbl_RID_TEMP.
FinSuppGrpDesc) AS FirstOfFinSuppGrpDesc, tbl_RID_TEMP.Position, Max
(tbl_RID_TEMP.Seq) AS MaxOfSeq, tbl_RID_TEMP.BuyFromBP,
First(tbl_RID_TEMP.
BuyFromBPName) AS FirstOfBuyFromBPName, First(tbl_RID_TEMP.ShipFromBP) AS
FirstOfShipFromBP, First(tbl_RID_TEMP.ShipFromBPName) AS
FirstOfShipFromBPName, First(tbl_RID_TEMP.PurchPriceUnit) AS
FirstOfPurchPriceUnit, First(tbl_RID_TEMP.ConvFactorPriceInv) AS
FirstOfConvFactorPriceInv, First(tbl_RID_TEMP.ItemGrp) AS FirstOfItemGrp,
First(tbl_RID_TEMP.ItemGrpDesc) AS FirstOfItemGrpDesc, First(tbl_RID_TEMP.
StatGrp) AS FirstOfStatGrp, First(tbl_RID_TEMP.StatGrpDesc) AS
FirstOfStatGrpDesc, First(tbl_RID_TEMP.OrdDateLine) AS FirstOfOrdDateLine,
First(tbl_RID_TEMP.PlanDelDate) AS FirstOfPlanDelDate, First(tbl_RID_TEMP.
CurrPlanDelDate) AS FirstOfCurrPlanDelDate,
First(tbl_RID_TEMP.ConfDelDate)
AS FirstOfConfDelDate, First(tbl_RID_TEMP.ChngDelDate) AS
FirstOfChngDelDate,
Max(tbl_RID_TEMP.OrdQty) AS MaxOfOrdQty, First(tbl_RID_TEMP.PurchUnit) AS
FirstOfPurchUnit, First(tbl_RID_TEMP.ConverFactorPriceInv) AS
FirstOfConverFactorPriceInv, Sum(tbl_RID_TEMP.QtyDel) AS SumOfQtyDel,
First
(tbl_RID_TEMP.RejQty) AS FirstOfRejQty, First(tbl_RID_TEMP.BackOrdQty) AS
FirstOfBackOrdQty, First(tbl_RID_TEMP.TaxCode) AS FirstOfTaxCode, First
(tbl_RID_TEMP.Whse) AS FirstOfWhse, First(tbl_RID_TEMP.WhseDesc) AS
FirstOfWhseDesc, First(tbl_RID_TEMP.TotalApprQty) AS FirstOfTotalApprQty,
First(tbl_RID_TEMP.CntrNumb) AS FirstOfCntrNumb, First(tbl_RID_TEMP.
CntrPosition) AS FirstOfCntrPosition, First(tbl_RID_TEMP.CostCompon) AS
FirstOfCostCompon, First(tbl_RID_TEMP.CostComponDesc) AS
FirstOfCostComponDesc, First(tbl_RID_TEMP.Insp) AS FirstOfInsp, Max
(tbl_RID_TEMP.ActDelDate) AS MaxOfActDelDate,
First(tbl_RID_TEMP.OrdConfDate)
AS FirstOfOrdConfDate, First(tbl_RID_TEMP.ReceiptNumb) AS
FirstOfReceiptNumb,
First(tbl_RID_TEMP.PackSlipNumb) AS FirstOfPackSlipNumb,
First(tbl_RID_TEMP.
PackSlipQty) AS FirstOfPackSlipQty, First(tbl_RID_TEMP.Canc) AS
FirstOfCanc,
First(tbl_RID_TEMP.ShipDate) AS FirstOfShipDate,
First(tbl_RID_TEMP.DelAmt)
AS FirstOfDelAmt, First(tbl_RID_TEMP.PriceMatrixDef) AS
FirstOfPriceMatrixDef,
First(tbl_RID_TEMP.ChngReasLines) AS FirstOfChngReasLines,
First(tbl_RID_TEMP.
ChngReasLinesDesc) AS FirstOfChngReasLinesDesc, First(tbl_RID_TEMP.
ChngReasTyp) AS FirstOfChngReasTyp, First(tbl_RID_TEMP.ChngReasTypDesc) AS
FirstOfChngReasTypDesc, First(tbl_RID_TEMP.Print) AS FirstOfPrint, First
(tbl_RID_TEMP.HdrOrdDateTime) AS FirstOfHdrOrdDateTime,
First(tbl_RID_TEMP.
OrdDateLineTime) AS FirstOfOrdDateLineTime, First(tbl_RID_TEMP.
ConfDelDateTime) AS FirstOfConfDelDateTime, First(tbl_RID_TEMP.
ChngDelDateTime) AS FirstOfChngDelDateTime, First(tbl_RID_TEMP.WhseAddr)
AS
FirstOfWhseAddr, First(tbl_RID_TEMP.WhseAddrName) AS FirstOfWhseAddrName,
First(tbl_RID_TEMP.Name) AS FirstOfName, First(tbl_RID_TEMP.[STOCKOUT
IND])
AS [FirstOfSTOCKOUT IND], First(tbl_RID_TEMP.[Boeing Follow-Up]) AS
[FirstOfBoeing Follow-Up], Max(tbl_RID_TEMP.Date_Added) AS
MaxOfDate_Added,
tbl_RID_TEMP.Proj_Combo, tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.
Orginating_Report
FROM tbl_RID_TEMP
GROUP BY tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd,
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
Click to expand...
 
Ken said:
The First aggregate function often is used as a way to avoid having to put
every single field into the GROUP BY clause in a totals query. If the value
in a field is essentially independent of the records (e.g., it's a company
name that is provided from a SELECT query that is combining data from
multiple tables), then using the First function means you don't need to put
that field in the GROUP BY clause at all. That keeps the GROUP BY clause
shorter and more manageable.

It also allows you to include a memo field in a totals query because using
First on a memo field does not truncate the field's data to 255 characters,
while putting the memo field in the GROUP BY clause will cause the field's
data to be truncated to 255 characters.

However, many people who are less experienced think that First function will
select the earliest record's value for that field (and similarly think that
Last function selects the latest record's value). This is incorrect. First
simply ignores the records and just selects the value from the first record
that the query "gets" that matches the query's setup requirements.
(Similarly, Last selects the value from the last record that the query
"gets".) The only way to get the value from the earliest record is to use an
ORDER BY clause that orders the data in ascending order according to the
field of interest, and then use the Min function. (Similarly, to get the
latest record's value, sort the data in ascending order and use the Max
function.)
Hi All,
Click to expand...
[quoted text clipped - 109 lines]
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
Click to expand...
Click to expand...

Ken this is outstanding and rightfully justifies your label as MSVP. This
was the exact answer I was looking for.

Your input is greatly appreciated.

Adamus Turner
 
wrote:
'First' was originally intended to refer to the first value in a
sorted list, just as Max gives the maximum value in a set.

It also happens to be faster than Min or Max, because it
doesn't have to look at or compare any records other than
the first one it finds.

Unfortunately, in Access 'First' is not reliable when used
with complex queries, and when used with an unsorted
query is just reliable enough to be dangerous.

So by looking at the query I can't tell if the designer was
just using 'first' to get a fast value from a poorly normalised
data source, or was just lucky that the unsupported behaviour
implied by the name 'first' gave him the results he wanted.

You will have to look at the data to see if he was just trying
to get a fast value from poorly normalised data - in which
case you can use a fast procedure that returns an unsorted
record - or if he really did think he was getting the first record
from a sorted set - in which case you will have to use a
slow procedure that returns a record from a sorted set.

(david)

returns unexpected results
if you don't watch what you are doing
Hi All,
Click to expand...
[quoted text clipped - 82 lines]
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
Click to expand...
Click to expand...

David,

I'm in a state of mystery of what he was trying to accomplish. I looked at
the result set and was able to replicate it, but I'm uncertain if my SQL
approach was accurate. I don't know if the was an Access master or an Access
rookie at this point. I'm in week 1 of this new job and am baffled by this
query.

Adamus
 
Hi,


If you have MS SQL Server 2005, you can use CLR to define an aggregate that
behaves like FIRST, or LAST.

First is easy since it "captures" the "first" value the database engine
presents to the group, and ignore further values presented (for the same
group, by the db engine). Last is also quite easy, it unconditionally
captures the value presented by the db engine, anytime.

By comparison with SUM, which start with an initialize with a NULL (result
that will be returned if all values for the group are null), then ignores
any null the db engine presents, captures the first not null value, then, if
any further not-null value is presented, add it to the (not-null) already
stored value,... you see that First and Last are almost an order of
magnitude easier to implement than Sum.


Hoping it may help,
Vanderghast, Access MVP


Adamus Turner said:
Hi All,

I'm hoping someone can explain this but first I'll give a little history.
I
was recently hired for a job and one task is to convert all Access queries
into SQL. I'm in the process of converting the following query and for the
life of me can't figure out what this guy was trying to do. Any advice on
how
to convert this to SQL would be utterly appreaciated: What are all these
"firsts" suppose to accomplish?

INSERT INTO tbl_All_Orders_Temp ( Company, Item, PurchOrd, DMOrdType,
HdrBuyFromBP, HdrBuyFromBPName, OrdType, OrdTypeDesc, HdrOrdDate, DelDate,
HdrWhse, HdrWhseDesc, Buyer, BuyerName, TermOfDel, TermOfDelDesc,
CarrierCd,
CarrierCdDesc, HdrConfDelDate, FinSuppGrp, FinSuppGrpDesc, [Position],
Seq,
BuyFromBP, BuyFromBPName, ShipFromBP, ShipFromBPName, PurchPriceUnit,
ConvFactorPriceInv, ItemGrp, ItemGrpDesc, StatGrp, StatGrpDesc,
OrdDateLine,
PlanDelDate, CurrPlanDelDate, ConfDelDate, ChngDelDate, OrdQty, PurchUnit,
ConverFactorPriceInv, QtyDel, RejQty, BackOrdQty, TaxCode, Whse, WhseDesc,
TotalApprQty, CntrNumb, CntrPosition, CostCompon, CostComponDesc, Insp,
ActDelDate, OrdConfDate, ReceiptNumb, PackSlipNumb, PackSlipQty, Canc,
ShipDate, DelAmt, PriceMatrixDef, ChngReasLines, ChngReasLinesDesc,
ChngReasTyp, ChngReasTypDesc, Print, HdrOrdDateTime, OrdDateLineTime,
ConfDelDateTime, ChngDelDateTime, WhseAddr, WhseAddrName, Name, [STOCKOUT
IND]
, [Boeing Follow-Up], Date_Added, Proj_Combo, Order_Combo,
Orginating_Report )

SELECT tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd,
First
(tbl_RID_TEMP.DMOrdType) AS FirstOfDMOrdType,
First(tbl_RID_TEMP.HdrBuyFromBP)
AS FirstOfHdrBuyFromBP, First(tbl_RID_TEMP.HdrBuyFromBPName) AS
FirstOfHdrBuyFromBPName, First(tbl_RID_TEMP.OrdType) AS FirstOfOrdType,
First
(tbl_RID_TEMP.OrdTypeDesc) AS FirstOfOrdTypeDesc, First(tbl_RID_TEMP.
HdrOrdDate) AS FirstOfHdrOrdDate, First(tbl_RID_TEMP.DelDate) AS
FirstOfDelDate, First(tbl_RID_TEMP.HdrWhse) AS FirstOfHdrWhse, First
(tbl_RID_TEMP.HdrWhseDesc) AS FirstOfHdrWhseDesc,
First(tbl_RID_TEMP.Buyer)
AS FirstOfBuyer, First(tbl_RID_TEMP.BuyerName) AS FirstOfBuyerName, First
(tbl_RID_TEMP.TermOfDel) AS FirstOfTermOfDel, First(tbl_RID_TEMP.
TermOfDelDesc) AS FirstOfTermOfDelDesc, First(tbl_RID_TEMP.CarrierCd) AS
FirstOfCarrierCd, First(tbl_RID_TEMP.CarrierCdDesc) AS
FirstOfCarrierCdDesc,
First(tbl_RID_TEMP.HdrConfDelDate) AS FirstOfHdrConfDelDate, First
(tbl_RID_TEMP.FinSuppGrp) AS FirstOfFinSuppGrp, First(tbl_RID_TEMP.
FinSuppGrpDesc) AS FirstOfFinSuppGrpDesc, tbl_RID_TEMP.Position, Max
(tbl_RID_TEMP.Seq) AS MaxOfSeq, tbl_RID_TEMP.BuyFromBP,
First(tbl_RID_TEMP.
BuyFromBPName) AS FirstOfBuyFromBPName, First(tbl_RID_TEMP.ShipFromBP) AS
FirstOfShipFromBP, First(tbl_RID_TEMP.ShipFromBPName) AS
FirstOfShipFromBPName, First(tbl_RID_TEMP.PurchPriceUnit) AS
FirstOfPurchPriceUnit, First(tbl_RID_TEMP.ConvFactorPriceInv) AS
FirstOfConvFactorPriceInv, First(tbl_RID_TEMP.ItemGrp) AS FirstOfItemGrp,
First(tbl_RID_TEMP.ItemGrpDesc) AS FirstOfItemGrpDesc, First(tbl_RID_TEMP.
StatGrp) AS FirstOfStatGrp, First(tbl_RID_TEMP.StatGrpDesc) AS
FirstOfStatGrpDesc, First(tbl_RID_TEMP.OrdDateLine) AS FirstOfOrdDateLine,
First(tbl_RID_TEMP.PlanDelDate) AS FirstOfPlanDelDate, First(tbl_RID_TEMP.
CurrPlanDelDate) AS FirstOfCurrPlanDelDate,
First(tbl_RID_TEMP.ConfDelDate)
AS FirstOfConfDelDate, First(tbl_RID_TEMP.ChngDelDate) AS
FirstOfChngDelDate,
Max(tbl_RID_TEMP.OrdQty) AS MaxOfOrdQty, First(tbl_RID_TEMP.PurchUnit) AS
FirstOfPurchUnit, First(tbl_RID_TEMP.ConverFactorPriceInv) AS
FirstOfConverFactorPriceInv, Sum(tbl_RID_TEMP.QtyDel) AS SumOfQtyDel,
First
(tbl_RID_TEMP.RejQty) AS FirstOfRejQty, First(tbl_RID_TEMP.BackOrdQty) AS
FirstOfBackOrdQty, First(tbl_RID_TEMP.TaxCode) AS FirstOfTaxCode, First
(tbl_RID_TEMP.Whse) AS FirstOfWhse, First(tbl_RID_TEMP.WhseDesc) AS
FirstOfWhseDesc, First(tbl_RID_TEMP.TotalApprQty) AS FirstOfTotalApprQty,
First(tbl_RID_TEMP.CntrNumb) AS FirstOfCntrNumb, First(tbl_RID_TEMP.
CntrPosition) AS FirstOfCntrPosition, First(tbl_RID_TEMP.CostCompon) AS
FirstOfCostCompon, First(tbl_RID_TEMP.CostComponDesc) AS
FirstOfCostComponDesc, First(tbl_RID_TEMP.Insp) AS FirstOfInsp, Max
(tbl_RID_TEMP.ActDelDate) AS MaxOfActDelDate,
First(tbl_RID_TEMP.OrdConfDate)
AS FirstOfOrdConfDate, First(tbl_RID_TEMP.ReceiptNumb) AS
FirstOfReceiptNumb,
First(tbl_RID_TEMP.PackSlipNumb) AS FirstOfPackSlipNumb,
First(tbl_RID_TEMP.
PackSlipQty) AS FirstOfPackSlipQty, First(tbl_RID_TEMP.Canc) AS
FirstOfCanc,
First(tbl_RID_TEMP.ShipDate) AS FirstOfShipDate,
First(tbl_RID_TEMP.DelAmt)
AS FirstOfDelAmt, First(tbl_RID_TEMP.PriceMatrixDef) AS
FirstOfPriceMatrixDef,
First(tbl_RID_TEMP.ChngReasLines) AS FirstOfChngReasLines,
First(tbl_RID_TEMP.
ChngReasLinesDesc) AS FirstOfChngReasLinesDesc, First(tbl_RID_TEMP.
ChngReasTyp) AS FirstOfChngReasTyp, First(tbl_RID_TEMP.ChngReasTypDesc) AS
FirstOfChngReasTypDesc, First(tbl_RID_TEMP.Print) AS FirstOfPrint, First
(tbl_RID_TEMP.HdrOrdDateTime) AS FirstOfHdrOrdDateTime,
First(tbl_RID_TEMP.
OrdDateLineTime) AS FirstOfOrdDateLineTime, First(tbl_RID_TEMP.
ConfDelDateTime) AS FirstOfConfDelDateTime, First(tbl_RID_TEMP.
ChngDelDateTime) AS FirstOfChngDelDateTime, First(tbl_RID_TEMP.WhseAddr)
AS
FirstOfWhseAddr, First(tbl_RID_TEMP.WhseAddrName) AS FirstOfWhseAddrName,
First(tbl_RID_TEMP.Name) AS FirstOfName, First(tbl_RID_TEMP.[STOCKOUT
IND])
AS [FirstOfSTOCKOUT IND], First(tbl_RID_TEMP.[Boeing Follow-Up]) AS
[FirstOfBoeing Follow-Up], Max(tbl_RID_TEMP.Date_Added) AS
MaxOfDate_Added,
tbl_RID_TEMP.Proj_Combo, tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.
Orginating_Report
FROM tbl_RID_TEMP
GROUP BY tbl_RID_TEMP.Company, tbl_RID_TEMP.Item, tbl_RID_TEMP.PurchOrd,
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
Click to expand...
 
Michel said:
Hi,

If you have MS SQL Server 2005, you can use CLR to define an aggregate that
behaves like FIRST, or LAST.

First is easy since it "captures" the "first" value the database engine
presents to the group, and ignore further values presented (for the same
group, by the db engine). Last is also quite easy, it unconditionally
captures the value presented by the db engine, anytime.

By comparison with SUM, which start with an initialize with a NULL (result
that will be returned if all values for the group are null), then ignores
any null the db engine presents, captures the first not null value, then, if
any further not-null value is presented, add it to the (not-null) already
stored value,... you see that First and Last are almost an order of
magnitude easier to implement than Sum.

Hoping it may help,
Vanderghast, Access MVP
Hi All,
Click to expand...
[quoted text clipped - 109 lines]
tbl_RID_TEMP.Position, tbl_RID_TEMP.BuyFromBP, tbl_RID_TEMP.Proj_Combo,
tbl_RID_TEMP.Order_Combo, tbl_RID_TEMP.Orginating_Report
Click to expand...
Click to expand...

Unfortunately, we're using SQL Server 2000, and if I'm understanding
everyone's responses correctly, all I should have to do is remove the first()
and place the fields in a GROUP BY clause and the resultset should be the
same. With the wide array of tasks, I haven't had a chance to compare
resultsets as of yet but I will post a reply when I do.

Thanks,

Adamus
 
Hi,


No! close call, but not necessary true in all cases. FIRST() picks value
from "a" single record "of the group". Here a counter example of what FIRST
do and in what doing what you said will produce a different result.


f1 f2 f3 'fields
alpha 1 2
alpha 2 1 'data sample


then


SELECT f1, FIRST(f2), FIRST(f3) FROM myTable GROUP BY f1

may return either row, but

SELECT f1, f2, f2 FROM myTable GROUP BY f1, f2, f3

returns the two rows.


If you have only one single FIRST(), you can change it for MIN(). If you
have more than one FIRST(), it does not always work (our example will return
{ alpha, 1, 1} which is NOT an initial record, while FIRST() would produce
an initial record). If you have more than one FIRST() and have a primary key
made of a single field, pk, then

SELECT a.* FROM myTable AS a WHERE a.pk IN( SELECT MIN(pk) FROM myTable
GROUP BY f1)

could do. If you have more than one FIRST and have a primary key made of
multiple fields, the IN() construction has to be changed into an EXISTS
construction; while if you have no primary key at all, ... you can make a
temp table with a UNIQUE constraint on the group, here f1, but with
IGNORE_DUP_KEY option on that index, then append the initial data to that
temp table. Sure, you have to read the result from this table, rather than
from a view/query



Hoping it may help,
Vanderghast, Access MVP


(...)
 
Back
Top