Welcome to weblogs.com.pk Sign in | Join | Help

Changing The Sort Order

Waseem asked a question, for which I am proposing a tricky solution.

 

Hi SQL Experts;
I am Facing a problem in my Project regarding  SQL Query, please help me to solve it.
I have A table named "Book"

 

BooK
__________
|BooKID      |                 <-- PK
|__________|
|BookName |
|__________|
|CategoryID|                  <-- FK
|__________|

Table Contains Values..
BooKID   --- BookName   --  CategoryID      
__________________________________
1           ---    Book1         --  1               
2           ---    Book2         --  1              
3           ---    Book3         --  2               
4           ---    Book4         --  3               
5           ---    Book5         --  3               
6           ---    Book6         --  2               


I want a query that returns me the rows sorted By "CategoryId" by the value i provide.

e.g
CategoryId =2
first it displays all rows having CategoryId =2  then
rest of rows
RESULT.
BooKID   --- BookName   --  CategoryID      
__________________________________
3           ---    Book3         --              
6           ---    Book6         --  2             
1           ---    Book1         --  1               
4           ---    Book4         --  3               
5           ---    Book5         --  3               
 

 

e.g
CategoryId =3
first it displays all rows having CategoryId =3  then
rest of rows
RESULT.

BooKID   --- BookName   --  CategoryID      
__________________________________
4           ---    Book4         --  3               
5           ---    Book5         --  3               
1           ---    Book1         --  1               
2           ---    Book2         --  1              
3           ---    Book3         --  2               
6           ---    Book6         --  2               


e.g

Thanx In Advance

M.Waseem.

 

Here is the solution:

 

--Initialization Statements, may give error

--only required when using some query tool

--you will not be using them in actual application

drop table books;

drop table #SortedBooks;

--Setting up your scenario

create table Books (

            BookId int not null primary key identity,

            BookName nvarchar(100),

            CategoryId int);

insert into Books (BookName, CategoryId) values ('First', 1);

insert into Books (BookName, CategoryId) values ('Second', 1);

insert into Books (BookName, CategoryId) values ('Third', 2);

insert into Books (BookName, CategoryId) values ('Fourth', 3);

insert into Books (BookName, CategoryId) values ('Fifth', 3);

--Assuming given id is 2

--The next code can be wrapped into some stored procedure

--consult SQL Books online to know how to create them

declare @id int;

select @id=2;

--Create temp table

create table #SortedBooks (

            MySortId int not null primary key identity,

            BookName nvarchar(100),

            CategoryId int);

insert into #SortedBooks (BookName, CategoryId)

            select BookName, CategoryId

            from Books where CategoryId = @id

            order by BookName;

insert into #SortedBooks (BookName, CategoryId)

            select BookName, CategoryId

            from Books where CategoryId <> @id

            order by CategoryId, BookName;

select CategoryId, BookName from #SortedBooks order by MySortId;

 

Points to Note

 

·          Temp tables are those which are created with hash prefix, i-e #SortedBooks

·          These tables are automatically destroyed when you close the database connection

·          You don’t need to implement naming scheme for concurrent connections, each connection can have the temp table with similar name, internally SQL Server takes care of them

Published Thursday, December 02, 2004 4:50 PM by khurram
Filed under:

Comments

# re: Changing The Sort Order

Sunday, December 05, 2004 2:34 AM by fahad
hmmmmmm... what about following ???


------------------------------------
select * from books where CategoryId = @Provided;

UNION

select * from books where CategoryId <> @Provided;
------------------------------------


eff_kay

# re: Changing The Sort Order

Sunday, December 05, 2004 9:23 PM by Khurram AzizT
If no order is defined, SQL uses its own strategy for emitting the query results, which I think is based on the clustered index of the table (or whatever key SQL is using in its internal B-Tree), so there is no guarantee that it will work. Therefore one needs an additional column.

On the same mailing list, Mark gave a simpler solution similar to the one I mentioned already, but without using temporary table.

declare
@CategoryID int

set @CategoryID = 3

-- get all the books that DO match the CategoryID passed in.
select 1 as Sort_Order, BookID, BookName, CategoryID
from Book
where
CategoryID = @CategoryID

union

-- Now use a union to get all books that do NOT match the CategoryID passed in.
select 2 as Sort_Order, BookID, BookName, CategoryID
from Book
where
CategoryID <> @CategoryID

-- And then sort by the spoofed field called Sort_Order
order by Sort_Order
New Comments to this post are disabled