Order By 1,2,3,4,5,6

[TJVFree]

I'm putting together a query that I would like in a certain order

I do have the numbers in each query. There there a way I can do this?

1 Dog
2 Cat
3 Horse
4 Pig
5 Cow

 

[PWise]

order by numberfield

 

[TJVFree]

I don't quit understand

Select Sort, LoanNumber, "LendersInspection", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from LoanDiscountFee_qry
UNION Select Sort, LoanNumber, "LoanOriginationFee", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from LoanOriginationFee_qry
order by sort ;

 

[TJVFree]

Sort LoanNumber Expr1002 BuyerCharge SellerCharge POCCharge POCPaidBy IsAPR FeeFamily
2 424510 LoanOriginationFee Yes Origination
2 5445121 LoanOriginationFee 1500 Yes Origination
3 424510 LoanOriginationFee Yes Origination
3 5445121 LoanOriginationFee 1500 Yes Origination

 

[PWise]

by what do you want it to sort

 

[TJVFree]

I’m trying to add a field called “Sort” in each of my querys. And then a number.
So it would look like this

Sort:”1”

Next query
Sort:"2

 

[PWise]

try

Select [COLOR=red] 1 as  [/color] Sort, LoanNumber, "LendersInspection", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from LoanDiscountFee_qry
UNION Select [COLOR=red] 2 as  [/color] Sort,  LoanNumber, "LoanOriginationFee", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from LoanOriginationFee_qry
order by sort ;

 

[TJVFree]

That didnt work, sorted the same way

1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
3
3
3
3
3

 

[PWise]

dont understand by what you want to sort
if you dont want to put the
1 as sort in you union query you can put in to your
LoanDiscountFee_qry
and
LoanOriginationFee_qry
quries

 

[TJVFree]

Am I missing something?

SELECT 1 AS Sort, x4b.LoanNumber, "LoanDiscountFee" AS LoanDiscountFee, x4b.LoanDiscountFeeBuyerCharge AS BuyerCharge, x4b.LoanDiscountFeeSellerCharge AS SellerCharge, x4b.LoanDiscountFeePOCCharge AS POCCharge, x4b.LoanDiscountFeePOCPaidBy AS POCPaidBy, x4b.LoanDiscountFeeIsAPR AS IsAPR, x4b.LoanDiscountFeeFeeFamily AS FeeFamily
FROM Testtable1;


SELECT 2 AS Sort, x4b.LoanNumber, "LoanDiscountFee" AS LoanDiscountFee, x4b.LoanDiscountFeeBuyerCharge AS BuyerCharge, x4b.LoanDiscountFeeSellerCharge AS SellerCharge, x4b.LoanDiscountFeePOCCharge AS POCCharge, x4b.LoanDiscountFeePOCPaidBy AS POCPaidBy, x4b.LoanDiscountFeeIsAPR AS IsAPR, x4b.LoanDiscountFeeFeeFamily AS FeeFamily
FROM Testtable2;


SELECT 3 AS Sort, x4b.LoanNumber, "LoanDiscountFee" AS LoanDiscountFee, x4b.LoanDiscountFeeBuyerCharge AS BuyerCharge, x4b.LoanDiscountFeeSellerCharge AS SellerCharge, x4b.LoanDiscountFeePOCCharge AS POCCharge, x4b.LoanDiscountFeePOCPaidBy AS POCPaidBy, x4b.LoanDiscountFeeIsAPR AS IsAPR, x4b.LoanDiscountFeeFeeFamily AS FeeFamily
FROM Testtable3;





Select Sort, LoanNumber, "LendersInspection", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from testtable1
UNION 
Select Sort, LoanNumber, "LendersInspection", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from testtable2
UNION 
Select Sort, LoanNumber, "LoanOriginationFee", BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from testtable3

 

[MajP]

What are you expecting to happen? If you order the union query on Sort, this will put all the records in the first query, then all the records in the second... But I am not certain that is what you want. I would think you would want something more like

424510 Lenders Inspection ....
424510 Loan Origination Fee

where you are sorting by loan number and then the queries for that loan number?

 

[TJVFree]

Exactly,

But want it to do this

1 424510 Lenders Inspection ....
2 424510 Lenders POC ....
3 424510 Loan Origination Fee
4 424510 Loan Origination Fee
5 424510 Loan POC
6 424515 Loan Origination Fee

 

[MajP]

going back to PWISE's 10:39. You would just modify that to read

order by 2, 1

This will sort first on the LoanNumber then on the query from which they came. If there are two records for a loan number in a single query they would get lumped together. I assume this is what is happening here:

3 424510 Loan Origination Fee
4 424510 Loan Origination Fee

 

[PWise]

try
order by loannumber, sort

 

[MajP]

I assume you are doing this to normalize the big excel table. To simplify this you could do it after the fact
make a table

tblTransactionType
transactionName
sortOrder

example
LendersInspection 1
LoanOriginationFee 2

Select  LoanNumber, "LendersInspection" As TransactionName, BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from LoanDiscountFee_qry
UNION   LoanNumber, "LoanOriginationFee" as TransactionName, BuyerCharge, SellerCharge, POCCharge, POCPaidBy, IsAPR, FeeFamily from LoanOriginationFee_qry

Now you can join tblTransactionType to your union query on TransactionName order by SortOrder

Makes it a little easier to change and modify.

 

[TJVFree]

Perfect. Thanks Majp

 

[TJVFree]

I’m wondering if there is a better work to do a “order by”
Below is the Union query I’m trying to get in order. Is there a better way?

SELECT 1 AS Sort, x4b.LoanNumber, "OurOriginationChargeFee" AS OurOriginationChargeFee, x4b.OurOriginationChargeFeeBuyerCharge AS  
BuyerCharge, x4b.OurOriginationChargeFeeSellerCharge AS SellerCharge, x4b.OurOriginationChargeFeePOCCharge AS POCCharge,  
x4b.OurOriginationChargeFeePOCPaidBy AS POCPaidBy, x4b.OurOriginationChargeFeeIsAPR AS IsAPR, x4b.OurOriginationChargeFeeFeeFamily AS  
FeeFamily
FROM x4b
Union
SELECT 2 AS Sort, x4b.LoanNumber, "LoanOriginationFee" AS LoanOriginationFee, x4b.LoanOriginationFeeBuyerCharge AS BuyerCharge,  
x4b.LoanOriginationFeeSellerCharge AS SellerCharge, x4b.LoanOriginationFeePOCCharge AS POCCharge, x4b.LoanOriginationFeePOCCharge AS  
POCPaidBy, x4b.LoanOriginationFeeIsAPR AS IsAPR, x4b.LoanOriginationFeeFeeFamily AS FeeFamily
FROM x4b
Union
SELECT 3 AS Sort, x4b.LoanNumber, "LoanDiscountFee" AS LoanDiscountFee, x4b.LoanDiscountFeeBuyerCharge AS BuyerCharge,  
x4b.LoanDiscountFeeSellerCharge AS SellerCharge, x4b.LoanDiscountFeePOCCharge AS POCCharge, x4b.LoanDiscountFeePOCPaidBy AS POCPaidBy,  
x4b.LoanDiscountFeeIsAPR AS IsAPR, x4b.LoanDiscountFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 4 AS Sort, x4b.LoanNumber, "AppraisalFee" AS AppraisalFee, x4b.AppraisalFeeBuyerCharge AS BuyerCharge, x4b.AppraisalFeeSellerCharge AS  
SellerCharge, x4b.AppraisalFeePOCCharge AS POCCharge, x4b.AppraisalFeePOCPaidBy AS POCPaidBy, x4b.AppraisalFeeIsAPR AS IsAPR,  
x4b.AppraisalFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 5 AS Sort, x4b.LoanNumber, "CreditReportFee" AS CreditReportFee, x4b.CreditReportFeeBuyerCharge AS BuyerCharge,  
x4b.CreditReportFeeSellerCharge AS SellerCharge, x4b.CreditReportFeePOCCharge AS POCCharge, x4b.CreditReportFeePOCPaidBy AS POCPaidBy,  
x4b.CreditReportFeeIsAPR AS IsAPR, x4b.CreditReportFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 6 AS Sort, x4b.LoanNumber, "LendersInspection" AS LendersInspection, x4b.LendersInspectionFeeBuyerCharge AS BuyerCharge,  
x4b.LendersInspectionFeeSellerCharge AS SellerCharge, x4b.LendersInspectionFeePOCCharge AS POCCharge, x4b.LendersInspectionFeePOCPaidBy AS  
POCPaidBy, x4b.LendersInspectionFeeIsAPR AS IsAPR, x4b.LendersInspectionFeeFeeFamily AS FeeFamily
FROM x4b;
union
SELECT "7" AS Sort, x4b.LoanNumber, "MortgageInsuranceApplicationFee" AS MortgageInsuranceApplicationFee,  
x4b.MortgageInsuranceApplicationFeeBuyerCharge AS BuyerCharge, x4b.MortgageInsuranceApplicationFeeSellerCharge AS SellerCharge,  
x4b.MortgageInsuranceApplicationFeePOCCharge AS POCCharge, x4b.MortgageInsuranceApplicationFeePOCPaidBy AS POCPaidBy,  
x4b.MortgageInsuranceApplicationFeeIsAPR AS IsAPR, x4b.MortgageInsuranceApplicationFeeFeeFamily AS FeeFeeFamily
FROM x4b
union
SELECT 8 AS Sort, x4b.LoanNumber, "AssumptionFee" AS AssumptionFee, x4b.AssumptionFeeBuyerCharge AS BuyerCharge,  
x4b.AssumptionFeeSellerCharge AS SellerCharge, x4b.AssumptionFeePOCCharge AS POCCharge, x4b.AssumptionFeePOCPaidBy AS POCPaidBy,  
x4b.AssumptionFeePOCPaidBy AS IsAPR, x4b.AssumptionFeePOCPaidBy AS FeeFamily
FROM x4b
union
SELECT 9 AS Sort, x4b.LoanNumber, x4b.BrokerFeeBuyerCharge AS BrokerFee, x4b.BrokerFeeBuyerCharge AS BuyerCharge, x4b.BrokerFeeSellerCharge AS SellerCharge, x4b.BrokerFeePOCCharge AS POCCharge, x4b.BrokerFeePOCPaidBy AS POCPaidBy, x4b.BrokerFeeIsAPR AS IsAPR, x4b.BrokerFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 10 AS Sort, x4b.LoanNumber, "CommitmentFee" AS CommitmentFee, x4b.CommitmentFeeBuyerCharge AS BuyerCharge,  
x4b.CommitmentFeeSellerCharge AS SellerCharge, x4b.CommitmentFeePOCCharge AS POCCharge, x4b.CommitmentFeePOCPaidBy AS POCPaidBy,  
x4b.CommitmentFeePOCPaidBy AS IsAPR, x4b.CommitmentFeePOCPaidBy AS FeeFamily
FROM x4b
union
SELECT 11 AS Sort, x4b.LoanNumber, "ProcessingFee" AS ProcessingFee, x4b.ProcessingFeeBuyerCharge AS BuyerCharge,  
x4b.ProcessingFeeSellerCharge AS SellerCharge, x4b.ProcessingFeePOCCharge AS POCCharge, x4b.ProcessingFeePOCPaidBy AS POCPaidBy,  
x4b.ProcessingFeePOCPaidBy AS IsAPR, x4b.ProcessingFeePOCPaidBy AS FeeFamily
FROM x4b
union
SELECT 12 AS Sort, x4b.LoanNumber, "TaxServiceFee" AS TaxServiceFee, x4b.TaxServiceFeeBuyerCharge AS BuyerCharge,  
x4b.TaxServiceFeeSellerCharge AS SellerCharge, x4b.TaxServiceFeePOCCharge AS POCCharge, x4b.TaxServiceFeePOCPaidBy AS POCPaidBy,  
x4b.TaxServiceFeePOCPaidBy AS IsAPR, x4b.TaxServiceFeePOCPaidBy AS FeeFamily
FROM x4b
union
SELECT 13 AS Sort, x4b.LoanNumber, "UnderwritingFee" AS UnderwritingFee, x4b.UnderwritingFeeSellerCharge AS SellerCharge,  
x4b.UnderwritingFeePOCCharge AS POCCharge, x4b.UnderwritingFeePOCPaidBy AS POCPaidBy, x4b.UnderwritingFeeIsAPR AS IsAPR,  
x4b.UnderwritingFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 14 AS Sort, x4b.LoanNumber, "ClosingFee" AS ClosingFee, x4b.ClosingFeeSellerCharge AS SellerCharge, x4b.ClosingFeePOCCharge AS  
POCCharge, x4b.ClosingFeePOCPaidBy AS POCPaidBy, x4b.ClosingFeeIsAPR AS IsAPR, x4b.ClosingFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 15 AS Sort, x4b.LoanNumber, "TitleSearchFee" AS TitleSearchFee, x4b.TitleSearchFeeSellerCharge AS SellerCharge,  
x4b.TitleSearchFeePOCCharge AS POCCharge, x4b.TitleSearchFeePOCPaidBy AS POCPaidBy, x4b.TitleSearchFeeIsAPR AS IsAPR,  
x4b.TitleSearchFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 16 AS Sort, x4b.LoanNumber, "TitleExamFee" AS TitleExamFee, x4b.TitleExamFeeSellerCharge AS SellerCharge, x4b.TitleExamFeePOCCharge AS  
POCCharge, x4b.TitleExamFeePOCPaidBy AS POCPaidBy, x4b.TitleExamFeeIsAPR AS IsAPR, x4b.TitleExamFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 17 AS Sort, x4b.LoanNumber, "BinderFee" AS BinderFee, x4b.BinderFeeSellerCharge AS SellerCharge, x4b.BinderFeePOCCharge AS POCCharge,  
x4b.BinderFeePOCPaidBy AS POCPaidBy, x4b.BinderFeeIsAPR AS IsAPR, x4b.BinderFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 18 AS Sort, x4b.LoanNumber, "DocPrepFee" AS DocPrepFee, x4b.DocPrepFeeSellerCharge AS SellerCharge, x4b.DocPrepFeePOCCharge AS  
POCCharge, x4b.DocPrepFeePOCPaidBy AS POCPaidBy, x4b.DocPrepFeeIsAPR AS IsAPR, x4b.DocPrepFeeFeeFamily AS FeeFamily
FROM x4b
union
SELECT 19 AS Sort, x4b.LoanNumber, "NotaryFee" AS NotaryFee, x4b.NotaryFeeSellerCharge AS SellerCharge, x4b.NotaryFeePOCCharge AS POCCharge,  
x4b.NotaryFeePOCPaidBy AS POCPaidBy, x4b.NotaryFeeIsAPR AS IsAPR, x4b.NotaryFeeFeeFamily AS FeeFamily
FROM x4b

 

[PHV]

I'm trying to get in order
Which order ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

like I said build this table

TblFeeTypes
FeeType SortOrder
OurOriginationChargeFee 1
LoanOriginationFee 2
LoanDiscountFee 3
AppraisalFee 4
CreditReportFee 5
...
NotaryFee 19


Then in your unions query you should provide them all the same alias not different alias and you can drop the sort order

SELECT LoanNumber, "OurOriginationChargeFee" AS FeeType
Union
SELECT LoanNumber, "LoanOriginationFee" AS FeeType
Union
SELECT LoanNumber, "LoanDiscountFee" AS FeeType,


Now when you are done with your big union query you can do a join on tblFeeTypes where BigUnionQuery.FeeType = TblFeeTypes.FeeType OrderBy LoanNumber, FeeType

This makes it a lot easier if you want to add more queries and fee types or change the order.

 

[Andrzejek]

BTW, you do not need to use aliases in most of your Select statements. The columns' names will be taken from the very first SELECT statement:

SELECT 1 [blue]AS Sort[/blue], x4b.LoanNumber, 
"OurOriginationChargeFee" [blue]AS OurOriginationChargeFee[/blue],
x4b.OurOriginationChargeFeeBuyerCharge [blue]AS BuyerCharge[/blue],
x4b.OurOriginationChargeFeeSellerCharge [blue]AS SellerCharge[/blue], 
x4b.OurOriginationChargeFeePOCCharge [blue]AS POCCharge[/blue],  
x4b.OurOriginationChargeFeePOCPaidBy [blue]AS POCPaidBy[/blue], 
x4b.OurOriginationChargeFeeIsAPR [blue]AS IsAPR[/blue], 
x4b.OurOriginationChargeFeeFeeFamily [blue]AS FeeFamily[/blue]
FROM x4b
Union
SELECT 2, x4b.LoanNumber, "LoanOriginationFee", 
x4b.LoanOriginationFeeBuyerCharge,  
x4b.LoanOriginationFeeSellerCharge, 
x4b.LoanOriginationFeePOCCharge, 
x4b.LoanOriginationFeePOCCharge, 
x4b.LoanOriginationFeeIsAPR, 
x4b.LoanOriginationFeeFeeFamily
FROM x4b
Union
SELECT 3, x4b.LoanNumber, ...

Have fun.

---- Andy