Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Query

Author

Topic

kumarpav
Starting Member

18 Posts

Posted - 2004-12-19 : 04:44:36

how to retrieve least 10 salaries of employees from a table?
i dont want to scan full table..

if i write query like this
"
select sal from emp where rownum<=10 orderby sal;
"
How the query working is "first query is taking top 10 records and then it is doing order by.."
here i am missing out remaining least salaries in table..

for this i written another query like this

select sal from (select sal from emp where order by sal)
emp where rownum<=10

it is working fine..but it is scanning full table..
here i have crores of records in emp table...
Order by is taking lot of time.....

can u plz help me?

kumarpav
Starting Member

18 Posts

Posted - 2004-12-19 : 07:02:40

For above problem I written query like this

select empno,pincode from emp a
where 10 >
(select count(*) from emp b
where a.sal>b.sal
and pincode like '40005%'
)
order by sal asc

it is displaying 62 records...
my records are displaying last in that list..

if i write like this

select empno,pincode from emp a
where 62 >
(select count(*) from emp b
where a.sal>b.sal
and pincode like '40005%'
)
order by sal asc

it is giving correct result...

i am maintaining my pincode as dynamically....
For different pcode it will give wrong result......

Plz can u write one query to " To display least 10 salaries of employees based on the pincode "

Can u do it urgent plz.....

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-19 : 08:44:19

[code]select a.empno, a.pincode, a.sal
where 10 < (select count(*) from emp b where b.pincode = a.pincode and b.sal > a.sal)
and a.pincode like('40005%')
order by a.pincode, a.empno, a.sal[/code]

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-19 : 09:09:59

select top 10 *
from emp
where pincode like '40005%'
order by ....

- Jeff

kumarpav
Starting Member

18 Posts

Posted - 2004-12-20 : 02:42:34

I tried with
select a.empno, a.pincode, a.sal
where 10 < (select count(*) from emp b where b.pincode = a.pincode and b.sal > a.sal)
and a.pincode like('40005%')
order by a.pincode, a.empno, a.sal

but it is giving only 5 records ...

if i put 6 instead of 10 in that query it is displaying my 10 records..
Actually for that query combination i have 15 records in my emp table.

Plz can u look in to this problem....and can u try to write in another way....

I tried with "TOP 10 * " thing also it is not working in my program..
Whatever may be i think it will scan full the table .. right?

plz try to rewrite the first query only..

Thanking u for ur time..

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-20 : 03:20:13

Right, reverse the inequalities...

select a.empno, a.pincode, a.sal
-- (select count(*) from emp b where b.pincode = a.pincode and b.sal < a.sal) as rank
from emp a
where 10 > (select count(*) from emp b where b.pincode = a.pincode and b.sal < a.sal)
and a.pincode like('40005%')
order by a.pincode, a.empno, a.sal

rockmoose

kumarpav
Starting Member

18 Posts

Posted - 2004-12-20 : 14:14:26

Now it is giving lot of rows.......
this query is not working....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-20 : 14:27:57

quote:

I tried with "TOP 10 * " thing also it is not working in my program..
Whatever may be i think it will scan full the table .. right?

no, it will be more efficient that what you are trying. Why didn't it work? How about some sample data and what results you are looking for?

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-20 : 14:51:50

Well it depends on what You want.
How many records do You want returned ?
a) just the least 10 salaries for all pincodes like('40005%')
b) the 10 least salaries for each pincode like('40005%')

--generate test data
create table #emp(empno int, pincode char(6), sal money)
insert #emp
select top 60 number+1, '40005' + ltrim(number%2+1), 1200-number*10 from master.dbo.spt_values where type = 'P'

print 'bottom 10 salaries for each pincode like(''40005%'')'
select a.empno, a.pincode, a.sal
from #emp a
where 10 > (select count(*) from #emp b where b.pincode = a.pincode and b.sal < a.sal)
and a.pincode like('40005%')
order by a.pincode, a.empno, a.sal

print 'bottom 10 salaries for all pincodes like(''40005%'')'
select a.empno, a.pincode, a.sal
from #emp a
where 10 > (select count(*) from #emp b where b.sal < a.sal)
and a.pincode like('40005%')
order by a.sal, a.pincode, a.empno

print 'data in emp table'
select empno, pincode, sal from #emp order by empno, pincode, sal

drop table #emp

If you just want 10 records (a),
you might as well use the TOP 10 that Jeff suggested.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources