Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bob Whatever
Starting Member
7 Posts |
 Posted - 2005-10-12 : 05:19:08
|
| Morning,Wondered if anyone can help me out with a sorting problem i have at the moment, basically, I have a table such as the followingAccountNo Income1 Income2 Income3 Income4 1111 20000 15000 35000 25000 2222 10000 20000 10000 0What I am looking for is some code that will allow me to return the incomes in descending order by row. So the output i would be looking for above would be:AccountNo Income1 Income2 Income3 Income4 1111 35000 25000 20000 15000 2222 20000 10000 10000 0I have tried using a CASE statement for this, but this will only return the maximum value, and if i run a second case statement where the income is not equal to the previous maximum, problems will occur where two incomes are the same as with account 2222.Can anyone help with this one or point me in the right direction?Many thanksBob'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-12 : 05:27:19
|
well if you could put the values in one column in a CSV form then you could use one user-defined function for this.if you must have 4 columns then i think you'll have to use the same number of functions as there are columns so that each function gets the proper value.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Bob Whatever
Starting Member
7 Posts |
Posted - 2005-10-12 : 05:51:05
|
| Unfortunately yes it has to have the four columns as it appears above, so that all the incomes are shown in the correct descending order...'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-10-12 : 08:13:22
|
Coming soon on dbdebunk ...quote:
Originally posted by spirit1
well if you could put the values in one column in a CSV form then you could use one user-defined function for this.
Ayway, Bob Whatever ... read up on first normal form.Jay White |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-12 : 08:37:35
|
 i can see it... ignorance to normal forms by spirit1 and bob whatever is a disgrace to industry and it's standards...people like those two should never be allowed to do any kind of sql programming ever...  Go with the flow & have fun! Else fight the flow |
|
|
|
Bob Whatever
Starting Member
7 Posts |
Posted - 2005-10-12 : 08:51:48
|
| My apologies if i havent served as much time in front of a monitor banging out SQL code as long as yourself Mr White. I apologise for wasting your precious time.Heres the answer i got anyway, basically adds four more columns to the end of the table that i am using, then organises these in order, then just update each column so that Income_Borrower_Sort4 = Income1, Income_Borrower_Sort3 = Income2 and so on....If anyone can show me a more effective way, I'd be grateful as ever.Cheers, Bob.DECLARE @Income_Borrower_Sort1 INT, @Income_Borrower_Sort2 INT, @Income_Borrower_Sort3 INT, @Income_Borrower_Sort4 INTDECLARE Sort_cursor CURSOR FOR SELECT [Income 1], [Income 2], [Income 3], [Income 4]FROM #MyTable OPEN Sort_cursorFETCH NEXT FROM Sort_cursor INTO @Income_Borrower_Sort1, @Income_Borrower_Sort2, @Income_Borrower_Sort3, @Income_Borrower_Sort4WHILE @@FETCH_STATUS = 0BEGIN EXEC DBC_CERepository.dbo.SortColumns @Income_Borrower_Sort1 OUTPUT, @Income_Borrower_Sort2 OUTPUT, @Income_Borrower_Sort3 OUTPUT, @Income_Borrower_Sort4 OUTPUT UPDATE #MyTable SET Income_Borrower_Sort1 = @Income_Borrower_Sort1, Income_Borrower_Sort2 = @Income_Borrower_Sort2, Income_Borrower_Sort3 = @Income_Borrower_Sort3, Income_Borrower_Sort4 = @Income_Borrower_Sort4 WHERE CURRENT OF Sort_cursor FETCH NEXT FROM Sort_cursor INTO @Income_Borrower_Sort1, @Income_Borrower_Sort2, @Income_Borrower_Sort3, @Income_Borrower_Sort4 END CLOSE Sort_cursor DEALLOCATE Sort_cursor'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-10-12 : 09:19:46
|
quote:
Originally posted by Bob Whatever
My apologies if i havent served as much time in front of a monitor banging out SQL code as long as yourself Mr White. I apologise for wasting your precious time....If anyone can show me a more effective way, I'd be grateful as ever.
I think that last blunt has got you a bit paranoid: you've read quite a bit into my nine word response.I guess your reading up on normalization didn't go so well. Maybe your problem is that you think being a good DBA requires lots of "banging out SQL code" ... when in fact, it really requires an understanding of the relational model, set mathematics, science and logic.Please, go ahead and continue with your current schema and now this new iterative bastardization of the relational model. All of us consultants are clapping with glee $$$ ...Hopefully some do-gooder numb-skull will come along behind me here and tell you exactly how your table should be created, show you how to convert your data over to a normalized model and write all your queries for you. I would have loved to help further, but you've closed that door ... I think I'll go bang something ...Jay White |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-12 : 09:22:07
|
| Bob -- just to let you know, if you designed the database properly you could do the sort you need by adding three words to a SQL Statement. Read up on normalization, it will save you loads of hassles and make your life much, much easier.Here's an analogy for you:A mechanic asks: "I have designed a car without wheels or a steering wheel. It moves very slowly and drags through the dirt, but it works and I am happy. However, it won't turn, it can only go straight ... How can I make it turn?" He is given two solutions:1) re-design the car properly, adding wheels and steering wheel, which is more work in the short term, but which results in a much better car that works better and easier and properly and follows agreed upon standards and works with existing tools.or2) Build a giant truck with a crane attached that follows your car around, and when you need to turn, it picks up your car, rotates it, and then re-lowers it to the ground.You have chosen option #2.Read up on normalization here: http://www.datamodel.org/NormalizationRules.htmlBest of luck! |
|
|
|
|
|
|
|
|
Sorting By Row