• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

changing order of group result

I have a simple query I need to change the order of the results.

Select id from header group by id
returns
1
10
2
25

I need it to return

1
2
10
25

Thanks
0
mwootencnc1
Asked:
mwootencnc1
  • 4
  • 4
  • 2
  • +3
1 Solution
 
rajvjaCommented:
Select id from header group by id order by id
0
 
Philip PinnellCommented:
Select id from header group by id order by 1
0
 
Philip PinnellCommented:
1 being column number
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the field is varchar data type, you will need to either change the field's data type to numerical data type (for example, int),
or have the on-the-fly cast to int:

order by cast(yourfield as int)

this will fail if you have non-numerical data, in which case you will need a more sophisticated method ...
0
 
Patrick MatthewsCommented:
If ID is using a [n][var]char data type, and if the entries can always be converted to a number:
Select id from header group by id order by CONVERT(int, id)

Open in new window

0
 
Patrick MatthewsCommented:
slow fingers :)
0
 
CluskittCommented:
instead of order by CONVERT(int, id), you can simply use order by id*1
0
 
mwootencnc1Author Commented:
I tried the different variations of the Order by statement and none of them worked.  Maybe I was incorrect by saying it was a query, I am actually trying to edit a stored procedure I posted the code.  The problem is when it gets the assemblyid from the the tbl.MbAssemblyHeader the results are only sorted by the 1st number not the actual numeric value.  Sometimes the assemblyid could be alphanumeric.  This is my 1st attemp at something like this so I apologize if I'm not asking the right question.
From dbo.tblSoTransHeader h Inner join dbo.tblSoTransDetail d
		on h.TransId = d.TransId
		Where Coalesce(d.ReqShipDate, h.ReqShipDate) Between @DateFrom And @DateThru
		And h.CustId Between @CustFrom And @CustThru
		And d.ItemId Between @AssemblyFrom And @AssemblyThru
		And d.ItemId in (Select AssemblyId From dbo.tblMbAssemblyHeader group by AssemblyId )
		And (h.TransType = 9 or h.TransType = 3) --9;New;3;Backordered
		And (Select Count(*) From dbo.tblMpOrder o inner join dbo.tblMpOrderReleases r
			On o.OrderNo = r.OrderNo
			Where r.EstCompletionDate Between @DateFrom And @DateThru --44853
			And o.AssemblyId = d.ItemId And r.CustId = h.CustId And r.SalesOrder = h.TransId And isnull(r.PurchaseOrder, '') = isnull(h.CustPoNum, '') and r.Status <> 1) = 0 --pet 43454
		And (Select Count(*) From dbo.tblMpHistoryOrderReleases r  --pet 42494
			Where r.EstCompletionDate Between @DateFrom And @DateThru --44853
			And r.AssemblyId = d.ItemId And r.CustId = h.CustId And r.SalesOrder = h.TransId And isnull(r.PurchaseOrder, '') = isnull(h.CustPoNum, '')) = 0 --pet 43454 
		AND d.Status = 0

Open in new window

0
 
CluskittCommented:
Use *1 on all arguments you want to convert from char to int.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Sometimes the assemblyid could be alphanumeric

please clarify with full data sample (with both numerical data and alphanumerical data) how the sorting should happen.

you might want to left-pad the numerical data so it's aligns right to ensure "requested" ordering:
http://www.developerdotstar.com/community/node/319

0
 
mwootencnc1Author Commented:
example data
11257
556
255888
2378
698745
mk5665
p644446
p858


should sort
556
2378
11257
255888
698745
mk5665
p858
p644446
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, so let's try
order by isnumeric(yourcol) desc
, case when isnumeric(yourcol) = 0 then yourcol 
  else right( '00000000000' + yourcol , 10 ) end

Open in new window

0
 
mwootencnc1Author Commented:
I get the following error when I modify the code from above and add your code

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
	Insert into #Orders (Type, OrderDate, ProdDate, SOTransId, CustId, PurchaseOrder, AssemblyId, LocId, UOMBase, Qty)
	Select 'C', h.TransDate, Coalesce(d.ReqShipDate, h.ReqShipDate) ReqShipDate, h.TransId, h.CustId, h.CustPONum, d.ItemId, d.LocId, d.UnitsBase 
		, Round((d.QtyOrdSell * Case When isnull(d.ConversionFactor, 0) = 0 Then 1 Else d.ConversionFactor End), @PrecQty) --pet 43453
		From dbo.tblSoTransHeader h Inner join dbo.tblSoTransDetail d
		on h.TransId = d.TransId
		Where Coalesce(d.ReqShipDate, h.ReqShipDate) Between @DateFrom And @DateThru
		And h.CustId Between @CustFrom And @CustThru
		And d.ItemId Between @AssemblyFrom And @AssemblyThru
		And d.ItemId in (Select AssemblyId From dbo.tblMbAssemblyHeader group by AssemblyId order by isnumeric(Assemblyid) desc
, case when isnumeric(assemblyid) = 0 then yourcol 
  else right( '00000000000' + yourcol , 10 ) end
 )
		And (h.TransType = 9 or h.TransType = 3) --9;New;3;Backordered
		And (Select Count(*) From dbo.tblMpOrder o inner join dbo.tblMpOrderReleases r
			On o.OrderNo = r.OrderNo
			Where r.EstCompletionDate Between @DateFrom And @DateThru --44853
			And o.AssemblyId = d.ItemId And r.CustId = h.CustId And r.SalesOrder = h.TransId And isnull(r.PurchaseOrder, '') = isnull(h.CustPoNum, '') and r.Status <> 1) = 0 --pet 43454
		And (Select Count(*) From dbo.tblMpHistoryOrderReleases r  --pet 42494
			Where r.EstCompletionDate Between @DateFrom And @DateThru --44853
			And r.AssemblyId = d.ItemId And r.CustId = h.CustId And r.SalesOrder = h.TransId And isnull(r.PurchaseOrder, '') = isnull(h.CustPoNum, '')) = 0 --pet 43454 
		AND d.Status = 0

	If @@Error <> 0 Set @RetVal = @@Error
	If @RetVal <> 0 Goto ErrorTrap
END
ELSE
BEGIN

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have a reason for the ORDER BY, as it's actually for a INSERT?
        Insert into #Orders (Type, OrderDate, ProdDate, SOTransId, CustId, PurchaseOrder, AssemblyId, LocId, UOMBase, Qty)
        Select TOP 100 PERCENT 'C', h.TransDate, Coalesce(d.ReqShipDate, h.ReqShipDate) ReqShipDate, h.TransId, h.CustId, h.CustPONum, d.ItemId, d.LocId, d.UnitsBase 
                , Round((d.QtyOrdSell * Case When isnull(d.ConversionFactor, 0) = 0 Then 1 Else d.ConversionFactor End), @PrecQty) --pet 43453
                From dbo.tblSoTransHeader h Inner join dbo.tblSoTransDetail d
                on h.TransId = d.TransId
                Where Coalesce(d.ReqShipDate, h.ReqShipDate) Between @DateFrom And @DateThru
                And h.CustId Between @CustFrom And @CustThru
                And d.ItemId Between @AssemblyFrom And @AssemblyThru
                And d.ItemId in (Select AssemblyId From dbo.tblMbAssemblyHeader group by AssemblyId order by isnumeric(Assemblyid) desc
, case when isnumeric(assemblyid) = 0 then yourcol 
  else right( '00000000000' + yourcol , 10 ) end
 )
                And (h.TransType = 9 or h.TransType = 3) --9;New;3;Backordered
                And (Select Count(*) From dbo.tblMpOrder o inner join dbo.tblMpOrderReleases r
                        On o.OrderNo = r.OrderNo
                        Where r.EstCompletionDate Between @DateFrom And @DateThru --44853
                        And o.AssemblyId = d.ItemId And r.CustId = h.CustId And r.SalesOrder = h.TransId And isnull(r.PurchaseOrder, '') = isnull(h.CustPoNum, '') and r.Status <> 1) = 0 --pet 43454
                And (Select Count(*) From dbo.tblMpHistoryOrderReleases r  --pet 42494
                        Where r.EstCompletionDate Between @DateFrom And @DateThru --44853
                        And r.AssemblyId = d.ItemId And r.CustId = h.CustId And r.SalesOrder = h.TransId And isnull(r.PurchaseOrder, '') = isnull(h.CustPoNum, '')) = 0 --pet 43454 
                AND d.Status = 0

        If @@Error <> 0 Set @RetVal = @@Error
        If @RetVal <> 0 Goto ErrorTrap
END
ELSE
BEGIN

Open in new window

0
 
mwootencnc1Author Commented:
You are correct I was able to get the code you provide working by using Select Top 100 percent but I think I applied to the wrong part of the procedure.  I am trying to look at the prodecure to see if I can use the same format further down.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now