ORDER BY column in ascending

Hello Experts,

I have SQL table Data that I want to Order by UpdateNumber Column ascending order but I cannot get it to work. I know this is just simple.

I really appreciate all the help. Please see attach.

Thank you.
OrderByUpdateNumber.xlsx
Queennie LAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

SELECT * FROM yourTable ORDER BY UpdateNumber ASC;

Open in new window

0
Queennie LAuthor Commented:
@ste5an:

I tried that is not working what I want to output.

Thank you for your help.
0
FiloCommented:
Can you post the exact script you tried?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Queennie LAuthor Commented:
@Filo:

select *
 FROM SQLTableData
 ORDER BY [Filename] ASC,[UniqueUpdate] ASC,[UniqueColumn7] ASC ,[UniqueLast2] ASC, [UpdateNumber] ASC,  [LineNumber] ASC

Thank you.
0
FiloCommented:
With this, you are sorting on multiple levels and your data on the spreadsheet reflect this. Do you want your FIRST sort to by by UpdateNumber? If so:

select * 
 FROM SQLTableData
 ORDER BY [UpdateNumber] ASC,  [Filename] ASC,[UniqueUpdate] ASC,[UniqueColumn7] ASC ,[UniqueLast2] ASC, [LineNumber] ASC

Open in new window

0
Queennie LAuthor Commented:
@Filo:

I did that query is not working with output I want.
0
aflockhartCommented:
What is it that determines the groups of items, in your spreadsheet ?  Why are the first 7 items in one sorted group and the next 5 items in a different group ?
0
ste5anSenior DeveloperCommented:
Post a concise and complete example. This includes table DDL and sample data INSERT statements as single runnable T-SQL script.

Describe your desired output.
0
Éric MoreauSenior .Net ConsultantCommented:
It looks like you have the column labelled "UpdateNumber" showing twice (columns K and X).

But that alone won't fix the issue. As others have said, something else is missing. What gave you the groups that you currently have?
0
Queennie LAuthor Commented:
@aflockhart:
What is it that determines the groups of items, in your spreadsheet ?  - Grouping starts on AAA until EEE. This is why it is impossible for me to order it by ascending.

Why are the first 7 items in one sorted group and the next 5 items in a different group ? - Not all grouping have grouping of AAA to EEE.

@ste5an:
Post a concise and complete example. This includes table DDL and sample data INSERT statements as single runnable T-SQL script.

Describe your desired output. - Please find attached file.

@EricMoreau:
It looks like you have the column labelled "UpdateNumber" showing twice (columns K and X). K is my sql table data. X is the output.

But that alone won't fix the issue. As others have said, something else is missing. What gave you the groups that you currently have? - I think this query is impossible to do. That is why I reached out for assistance.


This is my query to "UpdateNumber" column:

CASE
      WHEN [Column 0] = 'DDD'
         AND  [Column 1] = 'AG'
      THEN 1

    WHEN [Column 0] = 'DDD'
         AND  [Column 1] = '0K'
      THEN 2

    WHEN [Column 0] = 'EEE'
      THEN 3

    WHEN [Column 0] = 'AAA'
      THEN 4

     WHEN [Column 0] = 'BBB'
      THEN 5

     WHEN [Column 0] = 'CCC'
      THEN 6
 
ELSE 7

END AS [UpdateNumber]

Open in new window


Thank you again for all your help.
0
Éric MoreauSenior .Net ConsultantCommented:
but how do you sort your other columns.

if you simply order by your  [UpdateNumber], you will get all the 1 together, followed by all the 2, ...
0
aflockhartCommented:
OK - Assuming that you are starting a new 'group' every time you hit a row that contains "AAA":


Try something like this:  ( my test table is called Test1109 ; replace this throughout with your own table name)


select test1109.*, t4.startofgroup
from test1109 inner join 
(
select t3.rownumber, max(t3.startofgroupAAA) as startofgroup from

(select t1.rownumber, t2.rownumber as startofgroupAAA
from test1109 t1 
inner join test1109 t2
on t2.rownumber <=t1.rownumber
and t2.[column 0] ='aaa' ) t3
group by t3.rownumber ) t4
on test1109.rownumber=t4.rownumber
order by startofgroup,updatenumber

Open in new window


 On a very large dataset, it's likely to be very slow.  But it sorts ( I think) as you want.  Here is my test output:  ( the final column calculates the row of the highest numbered "AAA" that precedes the item, and uses that as the first sort key,

If any of your groups don't have "AAA" though, this would fail.

uniqueID      column 0      LineNumber      RowNumber      UpdateNumber      startofgroup
5      DDD      5      5      1      1
6      DDD      6      6      2      1
7      EEE      7      7      3      1
1      AAA      1      1      4      1
2      BBB      2      2      5      1
3      CCC      3      3      6      1
4      CCC      4      4      6      1
11      DDD      11      11      1      8
12      DDD      12      12      2      8
8      AAA      8      8      4      8
9      BBB      9      9      5      8
10      CCC      10      10      6      8
16      DDD      16      16      1      13
17      DDD      17      17      2      13
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aflockhartCommented:
I'd also be looking at how you gather the data in the first place, to avoid needing to do this !

Is there any way you can capture some kind of common 'marker' that identifies the whole of a group that needs to be sorted together ?
0
Queennie LAuthor Commented:
I updated what I really need:

1st Table- Main table show as is in order.

2nd Table - I need to get the UpdateNumber Order by ASC to get what I need in my third table updates by cursor.

3rd Table: Below is the Cursor

DECLARE @uniqueid int;
	DECLARE @LineNumber int;
	DECLARE @column2 nvarchar(150);
	DECLARE @last_column2 nvarchar(150);
	DECLARE @cnt int;

	DECLARE cur cursor for  
	SELECT [UpdateNumber], [column2]
	FROM MainTable
	ORDER BY [Filename],[UniqueUpdate],[UniqueColumn7] ,[UniqueLast2], [UpdateNumber]

	OPEN cur  
	FETCH NEXT FROM cur INTO @uniqueid, @column2

	SET @last_column2 = ''
	SET @cnt = 0

	WHILE @@fetch_status = 0  
	BEGIN  
       IF @last_column2 = '' or (LEN(@column2)>1 and @cnt > 1) 
			SET @last_column2 = @column2
			
	   UPDATE a 
	   SET [NewColumn] = @last_column2 
	   FROM MainTable  a
	   WHERE uniqueid = @uniqueid
	   SET @cnt = @cnt + 1
	   
       FETCH NEXT FROM cur INTO @uniqueid, @column2  
	END

	CLOSE cur;
	DEALLOCATE cur;

Open in new window


I don't know if this is possible. I want 2nd Table to work the ORDER by.

Thank you for all your help.
OrderByUpdateNumber_New.xlsx
0
aflockhartCommented:
Lots of things to sort out in that cursor code, but it's not really the same question any more …
0
Queennie LAuthor Commented:
@aflockhart:

Sorry I did not respond to you yesterday. I will test your query and get back to you.

The only thing I need is the "Order By UpdateNumber ASC". The rest of the cursor is just an explanation why I need it to order by ascending.

Thank you again.
0
ste5anSenior DeveloperCommented:
hmm, looks like what I wrote ;)
0
Éric MoreauSenior .Net ConsultantCommented:
>>"The only thing I need is the "Order By UpdateNumber ASC"

I already told you that you have 2 fields in your results named UpdateNumber .

Also, if you only sort by this field, you will get all the 1 sorted first, then all the 2, ... Is this what you want?
0
Queennie LAuthor Commented:
@Eric Moreau:

That is not the output I need.

Thank you.

@ste5an:

Thank you for sharing it.

@aflockhart:

WOW! Your query works.
0
Queennie LAuthor Commented:
Thank Experts for all your help.

I really appreciate it. Without your intelligence and expertise, I cannot make it.

Thank you again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.