October 16, 2009 at 4:24 am
tabrez.test (10/16/2009)
is there any query to get the below result
You want the 4th highest salary to sort first, then the rest of the rows, ordered by salary ascending? That can be done.
SELECT name, sal FROM
(SELECT name, sal, Row_Number() OVER (ORDER BY sal) as RowOrdering
FROM SomeTable) sub
ORDER BY
CASE RowOrdering WHEN 4 THEN 0 ELSE 1 END, sal
If that's not what you want, then you need to explain the required ordering based on existing columns in the table.
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
October 16, 2009 at 4:25 am
As Dave mentioned now you have an order, it can be coded.
I've rewritten my example using your example, plus added a variable for record you want first.
set nocount on
declare @firstpos int
declare @a table ([name] varchar(128), [sal] int)
insert into @a values ('aaa',1000)
insert into @a values ('bbb',2000)
insert into @a values ('ccc',3000)
insert into @a values ('ddd',4000)
insert into @a values ('eee',5000)
-- setting which record to display first
set @firstpos = 4
-- normal order
select [name] , [sal] from @a
order by ROW_NUMBER() over (order by sal)
-- record first
select [name], [sal] from @a
order by case when ROW_NUMBER() over (order by sal) = @firstpos then 0 else
ROW_NUMBER() over (order by sal) end
Steven
October 16, 2009 at 4:30 am
ill explain here in a better way here order is asc on sal
name sal
aaa 1000
bbb 2000
ccc 3000
ddd 4000
eee 5000
is there any query to get the below result
out put should be like below
ddd 4000
aaa 1000
bbb 2000
ccc 3000
eee 5000
October 16, 2009 at 4:32 am
Hi Gail,
I see both our solutions generate the same plan with those five records.
(SQL build 9.0.4053)
Steven
October 16, 2009 at 4:39 am
SQL 2008:
CREATE TABLE
dbo.Employee
(
ename CHAR(3) NOT NULL,
salary INTEGER NOT NULL
);
INSERT
dbo.Employee
VALUES
('abc', 2000),
('asd', 1000),
('azz', 5000),
('awe', 4000),
('qwe', 6000),
('rew', 3000),
('bbc', 9000)
SELECT ename,
salary
FROM dbo.Employee
ORDER BY
CASE
WHEN ROW_NUMBER() OVER (ORDER BY %%PhysLoc%%) = 6 THEN 0
ELSE ROW_NUMBER() OVER (ORDER BY %%PhysLoc%%)
END;
DROP TABLE
dbo.Employee;
SQL 2005:
CREATE TABLE
dbo.Employee
(
ename CHAR(3) NOT NULL,
salary INTEGER NOT NULL
);
INSERT dbo.Employee VALUES ('abc', 2000);
INSERT dbo.Employee VALUES ('asd', 1000);
INSERT dbo.Employee VALUES ('azz', 5000);
INSERT dbo.Employee VALUES ('awe', 4000);
INSERT dbo.Employee VALUES ('qwe', 6000);
INSERT dbo.Employee VALUES ('rew', 3000);
INSERT dbo.Employee VALUES ('bbc', 9000);
SELECT ename,
salary
FROM dbo.Employee
ORDER BY
CASE
WHEN ROW_NUMBER() OVER (ORDER BY %%LockRes%%) = 6 THEN 0
ELSE ROW_NUMBER() OVER (ORDER BY %%LockRes%%)
END;
DROP TABLE
dbo.Employee;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 16, 2009 at 4:52 am
Thanks Steve i got it nw
n thanxx fr all who have replyed
October 16, 2009 at 5:05 am
Yeah. So obviously just for fun ;c)
The 2008-only %%physloc%% is even more interesting since it gives an encoded form of the row locator.
For a heap, this will be the RID, and since rows are stored in a heap in strict insertion order (so long as no data modification has ever occurred) this means we *can* order by 'insertion order' - no matter what anyone says about sets not being ordered.
After all, any physical implementation will obviously have some kind of order which will often corresponds to the common-sense view that a table is ordered by insertion order.
Fun eh!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 16, 2009 at 5:07 am
%%lockres%% is undocumented, Paul certainly has a found a new use for it
From http://www.informit.com/articles/article.aspx?p=686168&seqNum=5
The second option for row and key resources is to use the %%lockres%% virtual column. This column contains the key hash or the row ID for index keys and heap rows, respectively.
So this goes out of order...
create unique clustered index idxEmployee on Employee(ename)
go
SELECT ename,
salary,
%%LockRes%%
FROM dbo.Employee
ORDER BY
CASE
WHEN ROW_NUMBER() OVER (ORDER BY %%LockRes%%) = 6 THEN 0
ELSE ROW_NUMBER() OVER (ORDER BY %%LockRes%%)
END;
October 16, 2009 at 5:11 am
Also the rowid is treated as a string
@paul-2 , yes fun
INSERT dbo.Employee VALUES ('abc', 2000);
INSERT dbo.Employee VALUES ('asd', 1000);
INSERT dbo.Employee VALUES ('azz', 5000);
INSERT dbo.Employee VALUES ('awe', 4000);
INSERT dbo.Employee VALUES ('qwe', 6000);
INSERT dbo.Employee VALUES ('rew', 3000);
INSERT dbo.Employee VALUES ('bbc', 9000);
INSERT dbo.Employee VALUES ('bbz', 9001);
INSERT dbo.Employee VALUES ('bbz', 9002);
INSERT dbo.Employee VALUES ('bbz', 9003);
INSERT dbo.Employee VALUES ('bbz', 9004);
INSERT dbo.Employee VALUES ('bbz', 9005);
INSERT dbo.Employee VALUES ('bbz', 9006);
go
SELECT ename,
salary,
%%LockRes%%
FROM dbo.Employee
ORDER BY
CASE
WHEN ROW_NUMBER() OVER (ORDER BY %%LockRes%%) = 6 THEN 0
ELSE ROW_NUMBER() OVER (ORDER BY %%LockRes%%)
END;
go
October 16, 2009 at 5:16 am
Tut, tut Paul... you're not advocating using undocumented features in production code are you? 😉
The %%PhysLoc%% doesn't really fit the bill here, as the 6th row to be inserted doesn't survive once the amount of data grows.
Try this (i've added an identity column so you can see the row returned)
CREATE TABLE
dbo.Employee
(
ename CHAR(3) NOT NULL,
salary INTEGER NOT NULL ,
id int identity
);
INSERT
dbo.Employee (ename, salary)
VALUES
('abc', 2000),
('asd', 1000),
('azz', 5000),
('awe', 4000),
('qwe', 6000),
('rew', 3000),
('bbc', 9000)
SELECT ename,
salary, ID
FROM dbo.Employee
ORDER BY
CASE
WHEN ROW_NUMBER() OVER (ORDER BY %%PhysLoc%%) = 6 THEN 0
ELSE ROW_NUMBER() OVER (ORDER BY %%PhysLoc%%)
END;
INSERT
dbo.Employee (ename, salary) select ename,salary from dbo.Employee
INSERT
dbo.Employee (ename, salary) select ename,salary from dbo.Employee
INSERT
dbo.Employee (ename, salary) select ename,salary from dbo.Employee
INSERT
dbo.Employee (ename, salary) select ename,salary from dbo.Employee
INSERT
dbo.Employee (ename, salary) select ename,salary from dbo.Employee
SELECT ename,
salary, ID
FROM dbo.Employee
ORDER BY
CASE
WHEN ROW_NUMBER() OVER (ORDER BY %%PhysLoc%%) = 6 THEN 0
ELSE ROW_NUMBER() OVER (ORDER BY %%PhysLoc%%)
END;
DROP TABLE
dbo.Employee;
Vary the number of times you execute
INSERT
dbo.Employee (ename, salary) select ename,salary from dbo.Employee
and you'll suddenly see that id 6 isn't the 6th row any more.
October 16, 2009 at 5:27 am
Ian,
:laugh: Me? No, never! :laugh:
Just me taking the answer as seriously as the OP took posting a question (see my sig block)...
But, if you want the %%physloc%% thing to work over multiple pages, we'll need to write some code to traverse the page links using DBCC IND and DBCC PAGE etc...lol!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply