[kai_itz me]

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.

[olibenu]

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.

[Atli]

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.

[kai_itz me]

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