sorting an alphanumeric field
-
Jeff, your performance test took about 1.0-1.5 seconds (absolute precise numbers) on my laptop.
I don't think that is bad for 100 000 records...
N 56°04'39.16"
E 12°55'05.25" -
If you are not sure don't say.
Otherwise people would consider you don't know what you are talking about.
Should your "not sure" be considered as an apology?
_____________
Code for TallyGenerator -
Normalization is just the way of storing data.
If it's stored correctly from the beginning there is no need for conversion.
I did not time "de-normalization part" in your script either.
_____________
Code for TallyGenerator -
Pray you'll be the only user forever.
Otherwise response time not gonna be that good.
And 100k rows is not a real life database size.
Remember, Excel can hold 65k rows.
And don't you think ordering is typically just a small part of a query?
_____________
Code for TallyGenerator -
Why should I pray?
I don't know, neither do you, if this routine is going to run twice every minute, or only once a month. Not even on how many records that are involved in this business task.
I would very much like to know how you came to your conclusion!
1) How do you know that 100k row is not "real" table? I have tables in production which only holds 20 records. And I have tables to hold 90 million records. It is the business rules (and system architect) that decides this. And it was you alone who suggested to test on 100k records.
2) No, Excel can hold 1048576 rows. Please refresh your knowledge as it is out-of-date. And also, who has spoken about Excel at all in this topic?
3) No, ordering can be very time consuming, depending on what to order by. If you think ordering is only a small part of a query, I now understand why you only timed that part in your rip-off.
Sergiy, you are a twit if you think you know more about this original poster's problem than anyone else here at SQLSERVERCENTRAL.
I responded to a very specific, well-defined and clear problem statement with a suggestion that does exactly what original poster want, according to the sample data and expected output he provided.
What have you done? You have only put the original poster down without even knowing what kind of data the sample data represents! And kicking on the only one (me) who actually helped him with a workable solution.
N 56°04'39.16"
E 12°55'05.25" -
I don't have to explain why to sort the records this way. It is not my business rule! It belongs to the original poster. Ask him!
N 56°04'39.16"
E 12°55'05.25" -
Bill,
Slight "lull" in the action here so I thought I'd check... was your question answered or did you have something else in mind? And, I didn't get the chance to ask before... what do these numbers represent and why must they be sorted the way you requested them to be sorted?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Although this is not Bill's original problem, a real-world example of the need for special sorting methods is with engineering drawings that can be labeled revision A, B, C, ..., AA, AB, AC etc. much like the columns in Excel (sorry). To be in chronological order, you would need them to appear in that same order - an ASCII sort would give A, AA, AB, AC, B and so forth which is incorrect.
-
I am not sure, but that sorting can be made with
SELECT *
FROM SomeTable
ORDER BY RIGHT(' ' + Col1, 3)
or
SELECT *
FROM SomeTable
ORDER BY LEN(Col1), Col1
N 56°04'39.16"
E 12°55'05.25" -
I would go with your second example, since it doesn't depend on the length of the string. So if the drawings went up to ZX, ZY, ZZ, AAA, AAB it would still give the right result.
-
I just realized that either example would work for up to three characters. I meant to say that the 2nd example would work with any number of characters.
-
Thanks for the example of what this type of sort might be used by... brought back some memories of how the Navy designates assemblies and sub-assemblies (A4A1A13CR5). Also, might need that type of sort for "wierd" part numbers.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And in properly designed database assemblies and sub-assemblies should be separated.
Right?
Especially if they mean something for users, and they do, if they ask for that kind of sorting.
_____________
Code for TallyGenerator -
Correct... in a properly designed database, the assemblies and sub-assemblies would not only be separated, but they would be "normalized" as well. Separate table would also be present to identify the assembly hierarchy... makes life a whole lot easier.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And you're going to have a real hard time finding anyone in this thread who stated that they shouldn't be separated if that's what they are. The beef was with your jumping to conclusions. To be honest, your conclusion is likely correct, and if I was a betting man, I'd bet that you are correct. I'd also bet that those who took issue with your stance would agree that you are likely correct. On the other hand, I'd also bet that heads would come up at least 1 time in 3 coin flips, as that's a smart bet. I still wouldn't "conclude" that it would happen, as it's quite possible that it wouldn't.
The key word above is "likely". I don't reach conclusions based on what is likely, I reach them based on facts. I can make educated guesses on what is likely, but they remain educated guesses. Until the OP returns and tells us their purpose, it is impossible to be sure that your conclusion, which again is likely the case, is in fact correct.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply