SQL Server Performance Forum – Threads Archive

one more homework 🙂

if u have been given an employee table with empid, name and salary , the salary varies from $1000 to $ 20000, what should be the query to find salaries from 1k to 10k in ascending order and from 11k to 20k in descending order in the same query..

---

Is homework or real problem? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

its a real problem,, i ws just kidding that its a homework..

---

It’s almost there. The 20k – 11k would appear first. I don’t know if that is alright or not. select salary from (select top 100 percent case when lessonid between 1000 and 10999 then salary when salary between 11000 and 20000 then -salary end as orderby, salary from tblEmployee where salary between 1000 and 20000 order by orderby) as ordertable John

---

Correct me if I’m wrong, but this *IS* a homework question. I guess nobody would model a table like that. I can’t think of any reliable way to do this other than:
USE Northwind CREATE TABLE #t (ID INT IDENTITY, CustomerID VARCHAR(50), PostalCode CHAR(5), Country VARCHAR(10)) INSERT INTO #t (CustomerID, PostalCode, Country)
SELECT CustomerID, PostalCode, Country
FROM Customers
WHERE Country = ‘Germany’
ORDER BY CustomerID DESC INSERT INTO #t (CustomerID, PostalCode, Country)
SELECT CustomerID, PostalCode, Country
FROM Customers
WHERE Country = ‘Mexico’
ORDER BY CustomerID SELECT *
FROM #t
ORDER BY ID DROP TABLE #t
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

You can use the Derived Table and TOP Clause. Sample :- Select * from
(Select Top 1000 * from Sam where sal between 1000 and 10000 order by sal asc) as AA
Union all
Select * from
(Select Top 100 * from Sam where sal between 11000 and 20000 order by sal desc) as BB Regards
Sivaraman Latchapathi

---

The output from such a query is in no way guaranteed to be sorted correctly as the ORDER BY is *NOT* applied to the outer SELECT statement. SQL Server 2005 is much more sensible to things like this and I’ve already several postings where poeple complained that such a construct is not sorted "correctly" —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

Small modification to make it work:
select t.*
from (Select * from
(Select Top 1000 * from Sam where sal between 1000 and 10000 order by sal asc) as AA
Union all
Select * from
(Select Top 100 * from Sam where sal between 11000 and 20000 order by sal desc) as BB
) as t
order by
case
when sal between 1000 and 10000 then sal
else 40000 – sal
end spacemonkey: select top 100 percent is not guaranted to return rows in an "order by" order. Seehttp://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx for explanation.

---

I still prefer Frank’s solution, maybe using table variable instead of temporary table.

---

I am not sure if which one should take precedence, (0K – 10K) or the (10K + 1 to 20K). I assume the second group, here’s the query: create table #temp(
id int identity(1,1) not null,
ename varchar(20),
salary money
)
go insert into #temp(ename, salary) values (‘john’,1000)
insert into #temp(ename, salary) values (‘doe’,8000)
insert into #temp(ename, salary) values (‘max’,11000)
insert into #temp(ename, salary) values (‘sigel’,20000)
go select ename, salary
from #temp
order by
case
when salary <= 10000 then 1000000 + salary
when salary > 10000 then 1000000 + (-1 * salary)
end
go drop table #temp result:
ename salary
——————– ———————
sigel 20000.0000
max 11000.0000
john 1000.0000
doe 8000.0000 May the Almighty God bless us all!
www.empoweredinformation.com

---

Nice, must remember that little trick. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

---

]]>