Order By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order By

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-10-01 : 06:35:15

Hi,

I have the following query and try to oder by Source_Supplier but it doesnt order it by it.

I still records displayed as;

Source_Supplier
2982
352
1
243

SELECT DISTINCT 
                      TOP (100) PERCENT 'GPRD' AS Source, t4.Oracle_no AS Source_Supplier, t2.pay_sort_code, t2.pay_acc_num, t2.post_code, t2.pay_method, 
                      'GBP' AS Currency, '0' AS Tax_Percentage, t3.ProjectDesc, '01-M0601-3472-000000-000000' AS Expense_Account, 
                      'GPRD' + ' ' + t2.pay_method AS Paygroup, '1-M0601-5501-000000-000000' AS Liability_Account, t1.Prac_no, t2.prac_eid, t2.payee_name, 
                      RIGHT('0000' + '' + CONVERT(nvarchar(20), t1.PaymentRunID), 4) AS PaymentRunID, dbo.qryGrossAmountPerPractice.GrossAmount, t1.ProjectID, 
                      t1.DatePaid
FROM         GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
                      dbo.tblProjectPatients AS t1 ON t2.prac_no = t1.Prac_no INNER JOIN
                      dbo.tblProjects AS t3 ON t1.ProjectID = t3.ProjectID INNER JOIN
                      GPRDTech.gprdsql.TblOracleNos AS t4 ON t2.prac_no = t4.Prac_no AND t2.pay_method = t4.Pay_method INNER JOIN
                      dbo.qryGrossAmountPerPractice ON t1.ProjectID = dbo.qryGrossAmountPerPractice.ProjectID AND 
                      t1.Prac_no = dbo.qryGrossAmountPerPractice.Prac_no INNER JOIN
                      dbo.tblCurrCommittProj ON t3.ProjectID = dbo.tblCurrCommittProj.Curr_ProjectID
WHERE     (t3.ProjectStatus = 'true') AND (t1.PaymentRunID IS NOT NULL) AND (t1.DatePaid IS NOT NULL)
ORDER BY Source_Supplier

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 06:52:58

Why SELECT DISTINCT TOP (100) PERCENT?

Isn't that just

SELECT DISTINCT

Does this work / give the same results


SELECT DISTINCT
	'GPRD' AS Source
	, t4.Oracle_no AS Source_Supplier
	, t2.pay_sort_code
	, t2.pay_acc_num
	, t2.post_code
	, t2.pay_method
	, 'GBP' AS Currency
	, '0' AS Tax_Percentage
	, t3.ProjectDesc
	, '01-M0601-3472-000000-000000' AS Expense_Account
	, 'GPRD' + ' ' + t2.pay_method AS Paygroup
	, '1-M0601-5501-000000-000000' AS Liability_Account
	, t1.Prac_no
	, t2.prac_eid
	, t2.payee_name
	, RIGHT('0000' + '' + CONVERT(nvarchar(20), t1.PaymentRunID), 4) AS PaymentRunID
	, dbo.qryGrossAmountPerPractice.GrossAmount
	, t1.ProjectID
	, t1.DatePaid
FROM
	GPRDTech.gprdsql.TblPracDetails AS t2
	INNER JOIN dbo.tblProjectPatients AS t1 ON t2.prac_no = t1.Prac_no
	INNER JOIN dbo.tblProjects AS t3 ON t1.ProjectID = t3.ProjectID

	INNER JOIN GPRDTech.gprdsql.TblOracleNos AS t4 ON
		t2.prac_no = t4.Prac_no
		AND t2.pay_method = t4.Pay_method

	INNER JOIN dbo.qryGrossAmountPerPractice ON
		t1.ProjectID = dbo.qryGrossAmountPerPractice.ProjectID
		AND t1.Prac_no = dbo.qryGrossAmountPerPractice.Prac_no

	INNER JOIN dbo.tblCurrCommittProj ON t3.ProjectID = dbo.tblCurrCommittProj.Curr_ProjectID
WHERE
	t3.ProjectStatus = 'true'
	AND t1.PaymentRunID IS NOT NULL
	AND t1.DatePaid IS NOT NULL
ORDER BY
	t4.Oracle_no

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-10-01 : 07:13:45

It provides the same results.

Order By t4.Oracle_no works but in this form.

Source_Supplier
2025
289
303
3643
385
465

Noticed the ordering, starting with 2..., then 3..., and finally 4..

I want to have;

Source_Supplier
289
303
385
465
2025
3643

Any help please...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 07:17:58

I take it that datatype is a string then and not a number.

The order by is correct in that it is alphanumerically ordering them.

If this field can hold a number *only* (it can't have any other characters in it) then,

1) WHY IS IT A STRING TYPE AND NOT A NUMBER TYPE

2) try


ORDER BY
    CAST(t4.Oracle_no AS INT)

But be careful if there are non numeric characters the query will fail with a conversion error.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources