How to Arrange columns in different order using Distinct 1

[DougP]

the reports needs to be in a specific column order:
SOWTracker,Vendor,ResourceLastName,ResourceFirstName, etc
But they want unique records for each person so I am putting ResourceLastName first with distinct.
I created a Grid and fill it with data but of course SOWTracker is not first.
If change my SQL string to: Select SOWTracker, Vendor, Distinct ResourceLastName ,
I get an error: Incorrect syntax near the keyword 'Distinct'.
The AS.NET GridView has no columns, its unbound, the columns are created by passing a SQL string to a data adaptor:
Is there a way to switch it so lastname is unique or distinct but SOWtracker is first on the left hand side?
Any ideas?

SQLStatement = "Select  Distinct ResourceLastName,ResourceFirstName,
SOWTracker,Vendor,ePrizeID,
convert(nvarchar(12),cast(StartDate as date), 101) as StartDate,
convert(nvarchar(12),cast(EndDate as date), 101) as EndDate,
CostCenter,Manager,
ResourceType,[Offshore-OnShore]  as OffshoreOnShore
from SOWResources 
Where [Vendor] like '%somedatahere%'"

        Dim daTrans As New SqlDataAdapter
        Dim dtTrans As New DataTable
        daTrans.SelectCommand = New SqlCommand(SQLStatement, Conn)
        daTrans.Fill(dtTrans)

TIA

DougP

 

[gmmastros]

The Distinct operator does not care about the column order. Distinct should come immediately after the Select keyword.

Distinct operates on all of the columns in the result set regardless of the column order.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

I want SOWtracker first after Select but Lastname Distinct, how do I do that?

DougP

 

[djj55]

SQLStatement = "Select Distinct SOWTracker, ResourceLastName,ResourceFirstName,
Vendor,ePrizeID,
convert(nvarchar(12),cast(StartDate as date), 101) as StartDate,
convert(nvarchar(12),cast(EndDate as date), 101) as EndDate,
CostCenter,Manager,
ResourceType,[Offshore-OnShore]  as OffshoreOnShore
from SOWResources 
Where [Vendor] like '%somedatahere%'"

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DougP]

djj55; Looks like that worked,
I guess I had some confusion somewhere before trying that.


DougP

 

[djj55]

Sometimes you get too close to the problem and need a fresh prospective.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!