3 Replies - 749 Views - Last Post: 05 August 2012 - 10:49 PM Rate Topic: -----

#1 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

sql query

Posted 04 August 2012 - 01:06 AM

hi there :

i have created 5 column in sql database (eid,ename,esurname,edept,esalary),,, further i have created ten departments
and i have total employee 50 with 5 person in each dept, now what i want is : top 1o person with max salary with id in odd serie (for e.g id should be either 1 or 3 or 5...)

i have tried so far,,but no luck ,,i need a hint,, help me

here is what i tried :

  select top 10 esalary as highestOne,ename,eid
  from(select *,ROW_NUMBER() over (partition by esalary order by eid desc)as row
  from empbiodata)as a 
  where (row%2)!=0
  order by esalary desc

This post has been edited by Atli: 04 August 2012 - 01:12 AM
Reason for edit:: Please use [code] tags when posting code.


Is This A Good Question/Topic? 0
  • +

Replies To: sql query

#2 olibenu  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 45
  • View blog
  • Posts: 537
  • Joined: 31-December 08

Re: sql query

Posted 04 August 2012 - 01:38 AM

i always break stuff like this to know where specifically the error comes from.
start from the inner nested statements until the outermost to see if they return the data you expect.
Was This Post Helpful? 1
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Re: sql query

Posted 04 August 2012 - 01:43 AM

Try removing the partition by esalary from your OVER clause. It doesn't make sense to partition it like that. It'll reset the row number count on every esalary value.
Was This Post Helpful? 1
  • +
  • -

#4 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: sql query

Posted 05 August 2012 - 10:49 PM

thank you guys for such a great help.. actually

i have tried this and got my result,, but i dont know whether it is correct way or not,, need help to varify
here is my current query :

select top 10 esalary as highestOne,ename,eid
from(select *,ROW_NUMBER() over (partition by esalary order by eid desc)as row
from empbiodata)as a
where (esalery%2)!=0
order by esalary desc
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1