Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, July 3, 2012 10:48 AM
I need to have the rows in a particular order without orderby clause.
for example table contains
Name Age
xyz 12
pqr 18
abc 20
mno 14
I want the table should be displayed in an order as the name appear in IN operator of SQL
e.g.
SELECT Name,Age
FROM tbl_Name
WHERE Name IN ('pqr','mno','xyz','abc')
it should display ...
Name Age
pqr 18
mno 14
xyz 12
abc 20
Any Suggestion would be appreciated I don't want to use any order by clause please remember.
Zishan Ansari .Net Developer
Tuesday, July 3, 2012 10:58 AM ✅Answered | 2 votes
No, the order of rows from a query is undefined (it can be whatever) unless you use ORDER BY in the query.
Tibor Karaszi, SQL Server MVP | web | blog
Tuesday, July 3, 2012 11:03 AM | 2 votes
You have to USE Order by clause to get the desired order: Here is one method you can try :
SELECT Name,Age
FROM yrtable t
cross apply
(
values('pqr',1),('mno',2),('xyz',3),('abc',4)
)tvc (N,I)
WHERE t.NAme=tvc.N
order by tvc.I
Thanks and regards, Rishabh K
Tuesday, July 3, 2012 11:19 AM
Just insert what ever data you have in a temp table and combine that with tbl_Name and order by asc. Below is an example script.
create table #temp
(id identity(1,1),
val varchar(100)
)
insert into #temp(val) values('pqr')
insert into #temp(val) values('mno')
insert into #temp(val) values('xyz')
insert into #temp(val) values('abc')
SELECT m.Name,m.Age
FROM tbl_Name as m inner join #temp as t ON m.Name = t.val
order by t.id asc
drop table #temp
Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.
Tuesday, July 3, 2012 1:27 PM
If you read the forums closely, you'll see lots of people who counted on "accidental" ordering of results from SQL 2000 and 2005, that no longer work in later versions, so counting on anomalies will sooner or later cause you trouble.
Here's a way, twice as long as it needs to be in order to bypass the ORDER BY statement.
Declare @Temp Table (Name varchar(99), Age int, RN int PRIMARY KEY) /* Making RN primary key is why the ordering works */
Insert @Temp
SELECT DT_Original.Name, Age, row_number() over(order by forceorder) as RN
FROM
(
Select 'abc' as Name, 10 as age UNION select 'mno', 11 UNION select 'pqr', 12 UNION select 'xyz', 13
) as DT_Original
Inner Join
(
Select 1 as ForceOrder, 'pqr' as Name
UNION Select 2, 'mno'
UNION Select 3, 'xyz'
UNION select 4, 'abc'
) as DT_ForceOrder on DT_forceorder.name = DT_original.name
Select * from @Temp
Speaking of "accidental orderings"... on my local machine, the highlighted code presents the data in order. In no way, does that mean you would or should count on that order to work forever!
You could also specifically do an ORDER BY ForceOrder above, given that an explicit method of sorting was provided in the derived table.
Clarify exactly why it's so important to you not to use an order by? Are you hoping to make a view out of this? Your only guaranteed way to get a view result in a particular order is with an ORDER BY. You could make a stored procedure out of the whole process, but is that simpler than just caving in, and using ORDER BY? ;-)
Tuesday, July 3, 2012 3:43 PM
Hi,
I don't think there is a way to do ordering without ORDER BY clause. Although the default order will depend on the clustered index, the ordering is not always guaranteed by the SQL Engine.
create table #temp (ID int, name varchar(10));
GO
insert into #temp values(3,'efg'),(1,'abc'),(2,'cde')
GO
select * from #temp
GO
CREATE CLUSTERED INDEX idx_temp on #temp (id)
GO
select * from #temp
--this is ordered on clustered key, however the ordering is not always guarenteed
Regards
Satheesh
Wednesday, July 4, 2012 11:13 AM
Thanks everybody for looking into my problem, I have implemented Rishabh's suggestion and its working fine
thanks a lot Rishabh :)
Zishan Ansari .Net Developer
Wednesday, June 5, 2013 9:50 PM
Funny, but this misconception seems to be too hard to grasp
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, June 6, 2013 12:10 AM
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats, which you did. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior ON SQL forums. Why were you rude?
>> I need to have the rows in a particular order without ORDER BY clause. <<
Please read the first chapters in any book on RDBMS. A table is a set; a set has no ordering by definition. What you see in the presentation layers of a tired architecture is a cursor. The ORDER BY clause is part of a cursor, not a SELECT.
>> I want the table to be displayed in the order as the name appear in IN operator of SQL <<
IN() is a predicate, not an operator!! The list form of the IN() predicate also has no ordering!! Have you ever read a single book on RDBMS? This is like being in a Geography class and asking if the world is flat, how the Sun goes around the Earth and how dragons in the Earth make earthquakes.
>> Any suggestion would be appreciated I don't want to use any ORDER BY clause please remember. <<
Well, SQL does not work that way. You also never read Codd's 12 rules, so you do not know the Information Principle. Any special ordering relation has to be shown as scalar values in the columns of the rows of a table.
You need to learn the absolve basics and we cannot give you an education on a forum.
--CELKO-- 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