April 1, 2009 at 1:12 pm
Anyone know how to sort this:
ID Col1 Col2 Col3
1 3 4 5
2 1 2 0
3 0 0 7
4 0 3 4
5 4 0 0
To look like this:
ID Col1 Col2 Col3
2 1 2 0
4 0 3 4
1 3 4 5
5 4 0 0
3 0 0 7
The zeros are just nulls and not considered in the sorting.
April 1, 2009 at 1:26 pm
I must be missing something. Why would a very simple Order By not do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2009 at 1:29 pm
Hi
Try this:
;WITH data (id, col1, col2, col3) AS
(
SELECT 1, 3, 4, 5
UNION SELECT 2, 1, 2, NULL
UNION SELECT 3, NULL, NULL, 7
UNION SELECT 4, NULL, 3, 4
UNION SELECT 5, 4, NULL, NULL
)
SELECT *
FROM data
ORDER BY CASE WHEN col2 IS NOT NULL THEN col2 ELSE 99 END
For next posts
Please have a look to the link in my signature. It makes it more simple for people to help you and you will get better, faster and more replies.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 1, 2009 at 1:58 pm
I'm not making judgements or trying to insult anyone's intelligence, but the sample I posted should be self explanatory. It's the people who can look at the example and fully grasp the problem without further explanation that will likely have the highest probability of solving it. Don't feel bad if you don't get it. Check back when a solution is found and it might be a good lesson to learn.
Sorry Flo, not quite the answer. Think larger. This table in the example is just a sample to illustrate a problem. Deliberately manipulating this table into the desired order is really not a challange. What I need is a solution that will also execute the same type of sort on larger tables with many more columns and rows.
April 1, 2009 at 2:26 pm
jsanborn (4/1/2009)
Anyone know how to sort this:
ID Col1 Col2 Col3
1 3 4 5
2 1 2 0
3 0 0 7
4 0 3 4
5 4 0 0
To look like this:
ID Col1 Col2 Col3
2 1 2 0
4 0 3 4
1 3 4 5
5 4 0 0
3 0 0 7
The zeros are just nulls and not considered in the sorting.
Where would you put this row?
ID Col1 Col2 Col3
6 3 0 9
Wherever it goes, it breaks a sequence. You haven't provided a precedence.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 1, 2009 at 2:28 pm
Hi jsanborn
jsanborn (4/1/2009)
I'm not making judgements or trying to insult anyone's intelligence, but the sample I posted should be self explanatory. It's the people who can look at the example and fully grasp the problem without further explanation that will likely have the highest probability of solving it. Don't feel bad if you don't get it. Check back when a solution is found and it might be a good lesson to learn.
It was just not really clear by the numbers you posted (you already formatted them, so they are more clear to read) what you want. What I wanted to say with the hint to the best-practices is: As you see in my post I had to format your data to create an answer. If you would do this formatting (as CTE, as temp table or variable table) more people would dive into your problem because they can immediately start with the solution not with the way to there ;-).
Sorry Flo, not quite the answer. Think larger. This table in the example is just a sample to illustrate a problem. Deliberately manipulating this table into the desired order is really not a challange. What I need is a solution that will also execute the same type of sort on larger tables with many more columns and rows.
So we need more information. You sample looked like "how to sort by a column and set NULL values to the end" for me. Since we don't know your real business case we are not able to handle it. Maybe post your real table DDL and some data which are more like your real data and I'm quiet sure that somebody will have an answer.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 1, 2009 at 2:34 pm
Unless the whole reason for your post was to pose a riddle, you might want to consider posting requirements on it.
Flo's answer doesn't force the order of the table. It just includes a table-create and inserting your sample data, which is what's usually necessary to resolve most non-trivial technical issues. The answer posted will sort the column regardless of whether it's 10 rows or 10-million rows, that's the purpose of the Order By clause.
As posted, the problem is very, very trivial, and just requires basic SQL knowledge to resolve. Anyone familiar with Order By can solve the problem exactly as posted. That's why I started by asking if there's more to this than meets the eye.
If, on the other hand, you are just learning T-SQL, and aren't familiar with Order By, it would be a good thing to say so, because then I can post both an answer to your immediate dillema, and an explanation of how it works, to help you learn.
So far as I can see, it has to be one (or both) of those two situations. Either there's more to this than what you posted, or you're looking for basic, beginning data. Which one of those is it? That will affect my answer on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 8:16 am
The values always increment across the row such that, when the rows are in order, the values in all columns also increment top to bottom. There are random cells that do not have a value (zero in this case) and are not considered in the sort. Here's another sample table that might make it a little clearer.
col1 col2 col3 col4 col5 col6 col7 col8
2.35 3.01 3.49 4.25 4.79 5.36 5.82 6.31
0 3.59 4.32 0 5.21 0 0 7.12
3.88 0 0 5.23 5.41 6.33 6.89 7.99
4.12 4.66 5.31 5.92 0 6.87 7.34 0
The rows of this table are in the correct order. The question is how to sort unordered rows into this order. Two things that should be immediatly obvious. Using "ORDER BY col1, col2" will not work. Also, using a case statement to include multiple columns simultaneously when zero is encountered will not work.
April 2, 2009 at 8:54 am
jsanborn (4/2/2009)
The values always increment across the row such that, when the rows are in order, the values in all columns also increment top to bottom. There are random cells that do not have a value (zero in this case) and are not considered in the sort. Here's another sample table that might make it a little clearer.
col1 col2 col3 col4 col5 col6 col7 col8
2.35 3.01 3.49 4.25 4.79 5.36 5.82 6.31
0 3.59 4.32 0 5.21 0 0 7.12
3.88 0 0 5.23 5.41 6.33 6.89 7.99
4.12 4.66 5.31 5.92 0 6.87 7.34 0
The rows of this table are in the correct order. The question is how to sort unordered rows into this order. Two things that should be immediatly obvious. Using "ORDER BY col1, col2" will not work. Also, using a case statement to include multiple columns simultaneously when zero is encountered will not work.
jsanborn you still haven't answered my question from above. DROP TABLE #test
CREATE TABLE #test (id INT, col1 INT, col2 INT, col3 INT)
INSERT INTO #test (id, col1, col2, col3)
SELECT 1, 3, 4, 5
UNION SELECT 2, 1, 2, NULL
UNION SELECT 3, NULL, NULL, 7
UNION SELECT 4, NULL, 3, 4
UNION SELECT 5, 4, NULL, NULL
--UNION SELECT 6, 3, NULL, 9
SELECT t.id, ISNULL(t.col1, 0) AS col1, ISNULL(t.col2, 0) AS col2, ISNULL(t.col3, 0) AS col3
FROM #test t,
(SELECT MAX(col1) AS col1, MAX(col2) AS col2, MAX(col3) AS col3 FROM #test) d
ORDER BY ISNULL(t.col1, d.col1) + ISNULL(t.col2, d.col2) + ISNULL(t.col3, d.col3), t.id DESC
Results
id col1 col2 col3
----------- ----------- ----------- -----------
2 1 2 0
4 0 3 4
1 3 4 5
5 4 0 0
3 0 0 7
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 2, 2009 at 9:03 am
So long as later columns always have either null or higher values than earlier columns, something like this will work:
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Col1 int,
Col2 int,
Col3 int);
insert into #T (Col1, Col2, Col3)
SELECT 3, 4, 5
UNION SELECT 1, 2, NULL
UNION SELECT NULL, NULL, 7
UNION SELECT NULL, 3, 4
UNION SELECT 4, NULL, NULL;
select *
from #T
order by coalesce(Col1, Col2, Col3), isnull(Col2, Col3), Col3;
On the other hand, the moment you have a row where an earlier column has a higher value than a later column, it won't produce the pattern you seem to want. Nor will it work if a row has an lower value in an early column and a higher value in a later column, than another row, like the example Chris provided.
Test it on your real data, using the "Order By coalesce()" function, and see if it will do what you need.
To see what I'm talking about with my caveats, try this:
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Col1 int,
Col2 int,
Col3 int);
insert into #T (Col1, Col2, Col3)
SELECT 3, 4, 5
UNION SELECT 1, 2, NULL
UNION SELECT NULL, NULL, 7
UNION SELECT NULL, 3, 4
UNION SELECT 4, NULL, NULL
UNION SELECT 3, null, 9
union select 5, 4, 3;
select *
from #T
order by coalesce(Col1, Col2, Col3), isnull(Col2, Col3), Col3;
You'll see that the 3, null, 9 row is out of order in the third column, and the 5,4,3 row is out of order in the second and third columns, but both are in order in the first column. So long as you can guarantee that rows with those characteristics won't exist, or that they will still follow your rules on sequence, this solution will work.
That's why I say there's more to this than meets the eye. Nobody on this site knows your data except you. Those two exceptions occurred to me, and they may require a more complex handling, or they may not.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:34 am
sort on coalesce of all columns
Far away is close at hand in the images of elsewhere.
Anon.
April 2, 2009 at 9:50 am
jsanborn (4/1/2009)
I'm not making judgements or trying to insult anyone's intelligence, but the sample I posted should be self explanatory. It's the people who can look at the example and fully grasp the problem without further explanation that will likely have the highest probability of solving it. Don't feel bad if you don't get it. Check back when a solution is found and it might be a good lesson to learn.
That's fairly condescending.
The reason we ask for the create table statements and the sample data in an easy-to-work-with format is so that we can easily test queries to see if they really do meet your requirements.
Many of the people helping out have minimal time to spend on the forums. Hence the easier it is to create a test table and get the sample data in, the greater the chance of them answering and giving you an answer that's been tested and does work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 10:13 am
the sample I posted should be self explanatory
Yes, it should have been. Obviously it wasn't.
A number of intelligent people agree on this point: You're asking for help. You have no standing to be condescending towards those who are trying to help you, and they have every right to expect complete and clear communications from you.
Take a few minutes to set up a clear and complete example, discuss the issues you have identified, and don't expect people to be telepathic and instantly familiar with your specific environment. You will get a solid answer fairly quickly. I have yet to see that approach fail on here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2009 at 10:33 am
There should be some description about the order in which you want to see the data.
By looking input and output we can have different meaning unless we know what is precedence.
April 2, 2009 at 11:04 am
Mr. Morris, if the question you are referring to is the one about the row with "3, 0, 9".
"The values always increment across the row such that, when the rows are in order, the values in all columns also increment top to bottom. There are random cells that do not have a value (zero in this case) and are not considered in the sort."
This should have adequately answered any questions.
I'm afraid the coalesce won't work either. It's essentially doing the same thing as the case statement. Comparing values from adjacent columns will not produce a correct result.
Viewing 15 posts - 1 through 15 (of 180 total)
You must be logged in to reply to this topic. Login to reply