December 11, 2012 at 6:48 am
DECLARE @T TABLE(ID INT,NAME VARCHAR(10))
INSERT INTO @T VALUES(1,'PPP')
INSERT INTO @T VALUES(2,'AAA')
INSERT INTO @T VALUES(2,'ZZZ')
INSERT INTO @T VALUES(3,'XYZ')
INSERT INTO @T VALUES(4,'PQR')
INSERT INTO @T VALUES(5,'ZAB')
SELECT * FROM @T WHERE NAME IN('XYZ','AAA','PQR')
IDNAME
----------
2AAA
3XYZ
4PQR
--But I need the result as below (i.e, the order should be same as how I had input the value in the IN() )
IDNAME
-----------
3XYZ
2AAA
4PQR
-- CAN YOU PLEASE GIVE ME A SINGLE QUERY WITHOUT DOING ANY FUNCTIONS OR TEMP TABLES
December 11, 2012 at 7:00 am
Maybe dynamic sql for the order by
A cte with union all for each value might do it.
Cursors never.
DTS - only when needed and never to control.
December 11, 2012 at 7:04 am
;with cte as
(
select val = 'XYZ', seq = 1
union all
select val = 'AAA', seq = 1
union all
select val = 'PQR', seq = 1
)
SELECT t.*
FROM @T t
join cte
on t.Name = cte.val
order by cte.seq
If you put the string into a variable you could use the cte to parse it into a table.
Cursors never.
DTS - only when needed and never to control.
December 11, 2012 at 7:11 am
You cannot force the order as per list in IN.
You should consider re-factoring your query.
One of the ways:
SELECT t.*
FROM @T t
JOIN (VALUES (1,'XYZ'),(2,'AAA'),(3,'PQR')) lst(rn,name)
ON lst.name= t.name
ORDER by lst.rn
December 11, 2012 at 7:38 am
Is the IN list an input parameter of some sort, or is it hard-coded into the query? You weren't clear on that, but it's the deciding factor on how you do something like 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
December 11, 2012 at 10:16 am
Logic ???? :w00t:
DECLARE @T TABLE(ID INT,NAME VARCHAR(10))
INSERT INTO @T VALUES(1,'PPP')
INSERT INTO @T VALUES(2,'AAA')
INSERT INTO @T VALUES(2,'ZZZ')
INSERT INTO @T VALUES(3,'XYZ')
INSERT INTO @T VALUES(4,'PQR')
INSERT INTO @T VALUES(5,'ZAB')
DECLARE @T_INPUT TABLE(ID int IDENTITY(1,1) , NAME VARCHAR(10))
INSERT INTO @T_INPUT VALUES('XYZ')
INSERT INTO @T_INPUT VALUES('AAA')
INSERT INTO @T_INPUT VALUES('PQR')
/* SELECT * FROM @T WHERE NAME IN('XYZ','AAA','PQR') */
SELECT AA.*
FROM @T AA,
@T_INPUT BB
WHERE AA.NAME=BB.NAME
ORDER BY BB.ID
December 11, 2012 at 10:29 am
....
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...
December 11, 2012 at 10:34 am
Eugene Elutin (12/11/2012)
Here I will play J.CELKO: ......
Steady on, don't overdo it :hehe:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 11, 2012 at 10:41 am
Phil Parkin (12/11/2012)
Eugene Elutin (12/11/2012)
Here I will play J.CELKO: ......
Steady on, don't overdo it :hehe:
I hope I didn't, as I have not complained about lack of PK and didn't state ISO numbers and didn't use "punch card" preamble... :hehe:
December 11, 2012 at 2:08 pm
CELKO (12/11/2012)
>> --But I need the result as below (i.e, the order should be same as how I had input the value in the IN() ) <<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.
Now, read that book on basic RDBMS you skipped and look for Dr. Codd's 12 rules. Look at the one known as “The Information Principle”, which tells us that ALL relationships are modeled as scalar values in the columns of rows in tables. Where is the column that models your ordering relationship? It does not exists!
What you want to do it fine in a punch card or magnetic tape file system, where the sequential records would provide the ordering you want. We would use an array for the IN list and loop from A[1] to A[n] , writing to a scratch tape in that order. But this is not SQL or RDBMS!
You mindset is wrong. Not a little off, but totally fundamentally wrong. You are the Flat Earth kid in Geography class, the creationist in Biology class, the Celestial Spheres advocate in an astronomy class. YOu have a lot of un-learning to do.
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.
- 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
December 11, 2012 at 2:13 pm
GSquared (12/11/2012)
CELKO (12/11/2012)
>> --But I need the result as below (i.e, the order should be same as how I had input the value in the IN() ) <<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.
Now, read that book on basic RDBMS you skipped and look for Dr. Codd's 12 rules. Look at the one known as “The Information Principle”, which tells us that ALL relationships are modeled as scalar values in the columns of rows in tables. Where is the column that models your ordering relationship? It does not exists!
What you want to do it fine in a punch card or magnetic tape file system, where the sequential records would provide the ordering you want. We would use an array for the IN list and loop from A[1] to A[n] , writing to a scratch tape in that order. But this is not SQL or RDBMS!
You mindset is wrong. Not a little off, but totally fundamentally wrong. You are the Flat Earth kid in Geography class, the creationist in Biology class, the Celestial Spheres advocate in an astronomy class. YOu have a lot of un-learning to do.
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 was thinking the exact same thing as soon as Joe brought up the punch card.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
December 12, 2012 at 2:53 am
Exactly as I thought - it's really hard to overdo the real master of "outburst"... :hehe:
December 12, 2012 at 3:00 am
I think we all wanted to say that :).
Cursors never.
DTS - only when needed and never to control.
December 12, 2012 at 11:00 pm
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?!?!?
December 13, 2012 at 9:30 am
Based on Dr Codd's rule, ALL existing relational DBMSs are "fundamentally wrong". NONE of them come close to implementing even the initial 12 "rules".
So, if we all behaved as Celko wishes, there'd be NO Oracle, NO Sybase, NO SQL Server, NONE of it.
In pursuit of theoretical perfection, we'd give up all the real gains made from a best-effort now.
The first plane was pitiful compared to what we use know. So should we have declared the Wright brothers had it "fundamentally wrong" and waited another 50 years to fly??
Sometimes we use the tools available to us as required by reality, not theory. Yes, fundamentals are important, but they can't be allowed to overshadow getting the job done. We've seen the effects of detached theory vs reality in the disaster that "classroom economics" has wrought in the real world.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply