Solved

changing order of group result

Posted on 2010-08-19
15
271 Views
Last Modified: 2012-06-21
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
Comment
Question by:mwootencnc1
  • 4
  • 4
  • 2
  • +3
15 Comments
 
LVL 11

Expert Comment

by:rajvja
Comment Utility
Select id from header group by id order by id
0
 
LVL 13

Expert Comment

by:Philip Pinnell
Comment Utility
Select id from header group by id order by 1
0
 
LVL 13

Expert Comment

by:Philip Pinnell
Comment Utility
1 being column number
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
slow fingers :)
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
instead of order by CONVERT(int, id), you can simply use order by id*1
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mwootencnc1
Comment Utility
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
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Use *1 on all arguments you want to convert from char to int.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 

Author Comment

by:mwootencnc1
Comment Utility
example data
11257
556
255888
2378
698745
mk5665
p644446
p858


should sort
556
2378
11257
255888
698745
mk5665
p858
p644446
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
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
 

Author Comment

by:mwootencnc1
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:mwootencnc1
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now