Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

ORDER BY Should be same as my input in IN() Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 6:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 5:41 AM
Points: 12, Visits: 32
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')

ID NAME
----------
2 AAA
3 XYZ
4 PQR

--But I need the result as below (i.e, the order should be same as how I had input the value in the IN() )

ID NAME
-----------
3 XYZ
2 AAA
4 PQR

-- CAN YOU PLEASE GIVE ME A SINGLE QUERY WITHOUT DOING ANY FUNCTIONS OR TEMP TABLES
Post #1395094
Posted Tuesday, December 11, 2012 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
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.
Post #1395102
Posted Tuesday, December 11, 2012 7:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
;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.
Post #1395104
Posted Tuesday, December 11, 2012 7:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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



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

How to post your question to get the best and quick help
Post #1395109
Posted Tuesday, December 11, 2012 7:38 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1395124
Posted Tuesday, December 11, 2012 10:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:57 AM
Points: 29, Visits: 989
Logic ????
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



Post #1395232
Posted Tuesday, December 11, 2012 10:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062


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


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

How to post your question to get the best and quick help
Post #1395236
Posted Tuesday, December 11, 2012 10:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 4,976, Visits: 11,666
Eugene Elutin (12/11/2012)


Here I will play J.CELKO: ......


Steady on, don't overdo it



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1395239
Posted Tuesday, December 11, 2012 10:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Phil Parkin (12/11/2012)
Eugene Elutin (12/11/2012)


Here I will play J.CELKO: ......


Steady on, don't overdo it


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


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

How to post your question to get the best and quick help
Post #1395242
Posted Tuesday, December 11, 2012 12:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
>> --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.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1395288
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse