ORDER BY Should be same as my input in IN()

Question

Post reply

ORDER BY Should be same as my input in IN()

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

nigelrivett SSCertifiable Points: 5362 More actions · Answer 1

Getting away from the original question but:

I don't think there is anything wrong with implementing something that goes against theory (as in database engines or most databases) as long as you know you are doing it and are willing to take the consequences.

Often it's just - "the people I'm working with can understand this", "this is the only thing I can implement in the time given", "my manager thinks he knows about databases".

Amazing how often you think short cuts will be OK but come back to bite you. It's worth being able to upset people, even yourself, with "told you so".

(Celko has a book on analytics - didn't know that - assume it's heavy on theory and worth having)

Cursors never.
DTS - only when needed and never to control.

GSquared SSC Guru Points: 260824 More actions · Answer 2

CELKO (12/12/2012)
Joe, considering the posts right before yours, this was a thing of beauty. Kind of like H-bomb explosions are a thing of beauty their own special way.
I should be stronger, but when I get a straight line like that, I just have to over-play it 😛
But to the point, look at his question. What is his assumption? Ordering in sets! It will take awhile, but instead of getting a dynamic SQL kludge or worse, he will now learn how to think in SQL and RDBMS. Much more useful than a kludge.

Oh, I totally agree with you on the theory. But I know of situations where a human being, usually a customer or a business user, needs to enter something like, "I want these three categories", and they expect them in the sequence they put them in.

There are easy ways to do that, but they depend on how the "list" (not "set", "list", since lists do have an order) arrives in the query.

As usual, my disagreement with you is your methodology, not the content of your post. The dunnage, as it were. No need to beat that dead horse any more though.

- 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

Josh Ashwood SSC Enthusiast Points: 102 More actions · Answer 3

CELKO (12/13/2012)
(Celko has a book on analytics - didn't know that - assume it's heavy on theory and worth having)
Nope! it is an overview for an application oriented programmer to get a look at the other side of the house. The strongest math is Pearson't r for correlations. The rest of it is cubes, rollups, star schema, etc.
I take a swipe at MDX in the last chapter.

What have you got against MDX Joe? It's actually a pretty powerful query language.. and since you love thinking in sets so much i'm surprised you would attack it. As a set based language.. it is miles ahead of SQL.

It has no cursors either :hehe:

Greg Snidow SSCoach Points: 16106 More actions · Answer 4

lokesha.b, I did not see it anywhere, though I may have missed it in the interim banter, but why do you need to do what you are asking? Is it purely accademic, or is there some real business need/request for it? As was hinted at earlier, if there is a real business need for it, (and in this case, that probably is that some muckety-muck who does not know better wants it that way, and you can't talk him/her out of it), it would probably be better handled on the front end.

P.S. - Keep up the good work Joe!

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

lokesha.b (12/11/2012)
-- CAN YOU PLEASE GIVE ME A SINGLE QUERY WITHOUT DOING ANY FUNCTIONS OR TEMP TABLES

SELECT * FROM @T WHERE NAME IN('XYZ','AAA','PQR')

ORDER BY CHARINDEX(NAME, '.XYZ.AAA.PQR.')

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 6

Eugene Elutin (12/11/2012)
....
SELECT AA.*
FROM @T AA,
@T_INPUT BB
WHERE AA.NAME=BB.NAME
ORDER BY BB.ID
Here I will play J.CELKO: Mate, you should really learn how to use ANSI standard JOINS...

Let's stick to the important stuff... like solving the problem. 😉

--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)

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 7

CELKO (12/11/2012)
You have no idea how RDBMS and SQL work. The list in the IN() predicate is a set; sets have no order. The result of a query is a set; sets have no ordering. Repeat that phrase until you understand it. SETS HAVE NO ORDERING.

Perhaps some clarification of that statement is in order. The clarification comes from your own post on this very thread.

CELKO (12/11/2012)
Where is the column that models your ordering relationship? It does not exists!

Sets CAN be ordered but, like you said, there has to be information in the set to support such ordering. Ordered sets appear in many forms from the simplicity of matryoshka dolls (ordered by physical size) and running totals (ordered by transaction date/time) to your own Nested Sets Hierarchies (order by relationship to other nodes). Without such ordering, those sets would be completely useless. They would be nothing but random shards.

Getting back to the original problem, let's state what you have stated, again...

CELKO (12/11/2012)
Where is the column that models your ordering relationship? It does not exists!

THAT IS what the OP is asking for help with! He wants the result [font="Arial Black"]SET [/font]to be sorted in the same order that he's presented the data in the IN() clause. Yes, we know that, by itself, that's not possible but you didn't need to go off on the OP with your insults and ineffective "hit 'em with a bat" attempts at teaching because the OP also knows that it cannot be done directly or he wouldn't be asking for help.

CELKO (12/12/2012)

But to the point, look at his question. What is his assumption? Ordering in sets! It will take awhile, but instead of getting a dynamic SQL kludge or worse, he will now learn how to think in SQL and RDBMS. Much more useful than a kludge.

You use an RDBMS in the very manner that you chastise other people for. Without an ordered set, your Nested Sets would be useless instead of the highly useful kludge that it actually is. In order to accomplish the kludge known as Nested Sets Hierarchies, you violated very nearly everything you've ever said about punch card programming, COBOL-like programming, and Codd's rules all in a single example of your own. It wouldn't have been so bad if you had actually processed each level in the hierarchy as a set but, instead, you used a very RBAR, 50's style, punch-card-like, mag tape row sequencer known as a "push-stack" and then processed the rows one at a time instead of as a set.

CELKO (12/12/2012)
I should be stronger, but when I get a straight line like that, I just have to over-play it 😛

Considering the kludges you've come up with and made a living on, such as the push-stack method of building Nested Sets, you should be a lot more tolerant of what people use SQL Server to do especially since you've done the same as them.

--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)

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 8

Josh Ashwood (12/12/2012)
Every time i open that database management window now.. that thought is there..
"Is what I am doing wrong.. not a LITTLE bit wrong.. but TOTALLY FUNDAMENTALLY WRONG?" :w00t:
Should I be buying a book on RDBMS and learning the earth is round?!?!?

The trouble is, the Earth is not perfectly round. Even if it were what people consider to be a "flat" Earth, it wouldn't be perfectly flat because there are things that range in size from an out-of-place pepple to mountains on its surface and depressions smaller than the footprint of an aphid to the great depressions that hold oceans. You have to have different tools to handle all of that. Sometimes, you have to realize that you can use a canoe to take a bath in. 😛

While writing code (T-SQL or otherwise), if you take on the attitude that you must always first question if what you're doing is "Fundamentally Wrong" according to others' say so, then you will suffer greatly from "paralysis by analysis" and there will be no innovation on your part. Breaking "rules" is one of the necessities to being innovative. Let other people's "rules" simply be a guide of what to watch for rather than being an obstacle. Despite the "rule" of using a screwdriver only to drive screws, it has a great many other uses that it does equally well. You can even "cut wood" with it if you're stuck in a wooden crate, have no other tools, and you need to get out. 😉 Of course, you also need to learn that using a screwdriver to drive screws isn't necessarily the best way to drive screws. :hehe:

--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)

Josh Ashwood SSC Enthusiast Points: 102 More actions · Answer 9

Well Joe I agree that the syntax of MDX is a bit disjointed. But it's become a bit of a standard now.. perhaps one day to even equal ANSI SQL? 😀

One positive thing about MDX is that the language seems to be complete enough, with no need for vendors requiring to add large bits of seemingly missing functionality to it... as has been done with the myriad of vendor additions to ANSI SQL? 😉

For all the cries about portability some may argue that the reason much SQL is not portable - from the design phase - is that one needs to use proprietary functionality to achieve what one needs to from the outset... therefore any hope of portability is instantly lost and falls by the wayside.

Now...back to porting this UPDATE FROM.. statement..

:w00t:

Josh Ashwood SSC Enthusiast Points: 102 More actions · Answer 10

While writing code (T-SQL or otherwise), if you take on the attitude that you must always first question if what you're doing is "Fundamentally Wrong" according to others' say so, then you will suffer greatly from "paralysis by analysis" and there will be no innovation on your part.

Indeed, I must confess that I have committed the following sins according to Celko :-

- Written more than three cursors in my lifetime

- Used UPDATE...FROM more than once

- Used user defined functions

- Used CROSS APPLY with proprietary XML functions

- Used an IDENTITY column as a primary key - heaps!

- Used cursors in a punch card fashion more than once, including nesting cursors

- Added query hints, force plans, etc to civilized ANSI SELECTs

I may be beyond saving ? 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Josh Ashwood (12/16/2012)
Indeed, I must confess that I have committed the following sins according to Celko :-
- Written more than three cursors in my lifetime

Check

- Used UPDATE...FROM more than once

Check

- Used user defined functions

Check

- Used CROSS APPLY with proprietary XML functions

Check

- Used an IDENTITY column as a primary key - heaps!

Check

- Added query hints, force plans, etc to civilized ANSI SELECTs

Check

Clearly I'm not a SQL expert like Joe is.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

Jeff Moden (12/15/2012)
Eugene Elutin (12/11/2012)
....
SELECT AA.*
FROM @T AA,
@T_INPUT BB
WHERE AA.NAME=BB.NAME
ORDER BY BB.ID
Here I will play J.CELKO: Mate, you should really learn how to use ANSI standard JOINS...
Let's stick to the important stuff... like solving the problem. 😉

You will find my solution on the first page of this discussion (3rd reply to the post, #1395109) 😛

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

carlosaamaral Old Hand Points: 325 More actions · Answer 13

Correction: this is my solution ... Eugene, did not like the use of non-ansi join... After.. Everything started ... 😀

be or not to be Ansi Join.... Is the question ..... :w00t:

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 14

CELKO (12/18/2012)

People used to say the same thing about FORTRAN (IBM, Univac, DEC, DG, Burroughs, NCR, CDC, Honeywell, et al). We just kept writing portable code and built hundreds of millions of lines of basic engineering routines.

BWAA-HAAA!!!! If you had used all of the horsepower available instead of limiting yourself to portable code, you probably could have done the job in just a couple of hundred K. 😀

--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)

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 15

CELKO (12/19/2012)
- Written more than three cursors in my lifetime
Five cursors, but you have to be old enough to not have had DRI actions.
- Used UPDATE...FROM more than once, etc.
This is like "I have eaten only one baby and I did not even finish it! Why are you calling me a cannibal?" :crying:
I may be beyond saving ? 🙂
No. Only COBOL aerograms are beyond redemption. Say ten "Hail, Codd's" and re-written five of your old programs into SQL Server 2012, ANSI only features.

But you certainly don't mind the stipends to your income that you receive by teaching 50's punch card and mag tape technology by using a RBAR While loop and push-stack to convert Adjacency Lists to Nested Sets. Your slate is far from clean. It is hypocritical of you to criticize anyone in Codd's good name.

--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)