Order by tweak

Question

Order by tweak

asita

SSCarpal Tunnel

Points: 4144
More actions
April 2, 2014 at 11:18 am

#291901

Hello Please help me,
i have a table with 500k rows, need to split into 3 sets(please assume need to insert 500k into 3 tables)
Segment column has 3 values SEG,MOR,CLN
Balance 0 to 100k
dept no 300 to 800
each set order by Segment, Balance desc
with out breaking dept no into multiple sets , so one deptno exist only in one set,
also fyi one dept exist in only one segment
Please help me with this
Thanks a ton in advance
Dhani

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

asita (4/2/2014)
Hello Please help me,
i have a table with 500k rows, need to split into 3 sets(please assume need to insert 500k into 3 tables)
Segment column has 3 values SEG,MOR,CLN
Balance 0 to 100k
dept no 300 to 800
each set order by Segment, Balance desc
with out breaking dept no into multiple sets , so one segment exist only in one set,
also fyi one dept exist in only one segment
Please help me with this
Thanks a ton in advance
Dhani

You haven't posted enough information for anybody to help. You talk about one table but you also mention three tables. You mention sets as coming from the Segment column and then you have an order by using that column.

You have been around here long to know that we need more details than this. Please take a few minutes and read the first article in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Sorry,

A table called pptList it has 500k rows, 7 columns

out of these 7 columns, we need to work around on 5 columns

which is

1) Segment column has 3 values SEG,MOR,CLN

2) Balance Column has values between 0 to 100k

3) dept_no Column has values between 300 to 800

4) Acct column for each Acct(fyi , an acct may exist more than one dept)

5) SETNO column which is null now, will update the value 1 or 2 or 3 (sets)

i would like to update setno column to be 1, 2, 3 order by segment column, then balance desc

but the critical is depet_no value (duplicated by acct) will be occur only in one SETNO(1 or 2 or 3)

if i do Order by Segment, Balancedesc then dept_no column is falling in more than one segment

if i do Order by deptno, Segment, Balancedesc then not meeting the requirement which is segment, balance high to low

hope this bit clear

Thank you in advance

Dhani

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You didn't follow the advice correctly.

You might need to use a ranking function to update your table but without ddl, sample data and expected results I can't be sure about that.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

asita (4/2/2014)
Sorry,
A table called pptList it has 500k rows, 7 columns
out of these 7 columns, we need to work around on 5 columns
which is
1) Segment column has 3 values SEG,MOR,CLN
2) Balance Column has values between 0 to 100k
3) dept_no Column has values between 300 to 800
4) Acct column for each Acct(fyi , an acct may exist more than one dept)
5) SETNO column which is null now, will update the value 1 or 2 or 3 (sets)
i would like to update setno column to be 1, 2, 3 order by segment column, then balance desc
but the critical is depet_no value (duplicated by acct) will be occur only in one SETNO(1 or 2 or 3)
if i do Order by Segment, Balancedesc then dept_no column is falling in more than one segment
if i do Order by deptno, Segment, Balancedesc then not meeting the requirement which is segment, balance high to low
hope this bit clear
Thank you in advance
Dhani

Not trying to be difficult here but put yourself in my shoes. I know nothing of your system or your database. I can't see your screen and I have no idea what you are trying to do. Now with that perspective in mind, read what you posted and ask yourself if you honestly think I should be able to offer any help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

sorry again, in a big stress, please excuse me

Hope this may help you,

create table #check1 (Segment varchar(3),balance numeric, deptno int, acct int,SetColumn int)

Insert into #check1 values('SEG',7000,300,5656,NULL)

Insert into #check1 values('SEG',4000,500,5656,NULL)

Insert into #check1 values('SEG',20,500,5622,NULL)

Insert into #check1 values('SEG',275,300,3356,NULL)

Insert into #check1 values('SEG',5000,300,3356,NULL)

Insert into #check1 values('MOR',17000,700,7756,NULL)

Insert into #check1 values('MOR',7000,800,7756,NULL)

Insert into #check1 values('MOR',20,700,2222,NULL)

Insert into #check1 values('MOR',275,700,1156,NULL)

Insert into #check1 values('MOR',5000,800,7856,NULL)

Insert into #check1 values('MOR',75,800,9956,NULL)

Insert into #check1 values('MOR',50,650,3356,NULL)

Insert into #check1 values('MOR',12550,650,9956,NULL)

select * from #check1 order by Segment , balance desc,

so now i would like to split this result to 3 sets (1,2,3) to set column, but the condition IS same deptno belongs TO same set

Thank you in advance

dhani

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

So based on that sample data, what's your expected output?

Does this help?

WITH CTE AS(

select *, DENSE_RANK() OVER(PARTITION BY Segment ORDER BY deptno) rankno

from #check1

)

UPDATE CTE SET

SetColumn = rankno

SELECT *

FROM #check1

order by Segment ,SetColumn, balance desc

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

asita SSCarpal Tunnel Points: 4144 More actions · Answer 7

Thank you Lous i appreciate your help, greatful to you