By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

DB2 Query problem

P: n/a
Hi,

I have the following data in my DB2 table ("Employee") :

EMPID MONTH SALARY
E340 JAN 3000
E340 FEB 2000
E340 MAR 3000
E340 APR 4000
E341 JAN 3000
E341 FEB 2000
E341 MAR 3000
E341 APR 4000

In the above data, i have four records each for two employees with
employee-id E340 and E341.
My requirement is that i want the data for the third row of each
employee using only the Employee-id . I do not want to use a WHERE
clause for month in my select query.

The following SELECT query will give me all the four records for a
particular employee-id:
SELECT * FROM EMPLOYEE where EMPID='E340'.

I do not want all the four records but only the 3rd record for each
employee. Is there a way in which the above query can be modified to
provide only the third row data ?

Thanks for your time,
Sachin

Mar 24 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
sunny wrote:
Hi,

I have the following data in my DB2 table ("Employee") :

EMPID MONTH SALARY
E340 JAN 3000
E340 FEB 2000
E340 MAR 3000
E340 APR 4000
E341 JAN 3000
E341 FEB 2000
E341 MAR 3000
E341 APR 4000

In the above data, i have four records each for two employees with
employee-id E340 and E341.
My requirement is that i want the data for the third row of each
employee using only the Employee-id . I do not want to use a WHERE
clause for month in my select query.

The following SELECT query will give me all the four records for a
particular employee-id:
SELECT * FROM EMPLOYEE where EMPID='E340'.

I do not want all the four records but only the 3rd record for each
employee. Is there a way in which the above query can be modified to
provide only the third row data ?

Thanks for your time,
Sachin

Third in what sense? Order is not defined for a table (including a
result table), until you specify an ORDER BY clause. Third month
ascending? Third highest/lowest salary? You could look at the RANK or
ROWNUMBER...OVER in the db2 docs at:
http://publib.boulder.ibm.com/infoce.../v8//index.jsp
Mar 24 '06 #2

P: n/a
>> My requirement is that i want the data for the third row of each employee using only the Employee-id .<<
Q1) Each employee-ids are same. So, how to distinguish each row.
Q2) What order of third row? If you don't care ordered by which column,
are there any meaning in the "third". third row ordered by MONTH and
third row ordered by SALARY may different.

Mar 24 '06 #3

P: n/a
Hi,

Let's assume that the data is ordered by month and the records for an
employee ar distinguished by month. I hope this will make it more clear
and undestandable,

Thanks,
Sachin

Mar 24 '06 #4

P: n/a
"sunny" <sa*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Hi,

Let's assume that the data is ordered by month and the records for an
employee ar distinguished by month. I hope this will make it more clear
and undestandable,

Thanks,
Sachin


In a relational database, there is no guarantee that the data will be
returned to the program in any particular order, or even returned in the
same order that the data is stored in, unless you specify "ORDER BY" (or
other equivalent statement).

There are no exceptions, decision of the judges is final, no refunds or
exchanges will be given.
Mar 24 '06 #5

P: n/a
Sachin,

Query :
select * from (select rownumber() over (order by empid) as row_number,
empid,salary from siebel.employee where empid='E340') as t where
row_number=3
And there is always more than one way to get work done!!!

Hi,

I have the following data in my DB2 table ("Employee") :

EMPID MONTH SALARY
E340 JAN 3000
E340 FEB 2000
E340 MAR 3000
E340 APR 4000
E341 JAN 3000
E341 FEB 2000
E341 MAR 3000
E341 APR 4000

In the above data, i have four records each for two employees with
employee-id E340 and E341.
My requirement is that i want the data for the third row of each
employee using only the Employee-id . I do not want to use a WHERE
clause for month in my select query.

The following SELECT query will give me all the four records for a
particular employee-id:
SELECT * FROM EMPLOYEE where EMPID='E340'.

I do not want all the four records but only the 3rd record for each
employee. Is there a way in which the above query can be modified to
provide only the third row data ?

Thanks for your time,
Sachin


Mar 24 '06 #6

P: n/a
Thanks a lot guys.

I tried using the resolution with ROW_NUMBER() but it turns out that
ROW_NUMBER() is not supported in OS/390 DB2 which i am using.
Is there a way of doing this without using the ROW_NUMBER() function.

Thanks,
Sachin

Mar 24 '06 #7

P: n/a
sunny wrote:
Thanks a lot guys.

I tried using the resolution with ROW_NUMBER() but it turns out that
ROW_NUMBER() is not supported in OS/390 DB2 which i am using.
Is there a way of doing this without using the ROW_NUMBER() function.

Thanks,
Sachin

Try this (untested)
SELECT EMPID, MONTH, SALARY
FROM EMPLOYEE E
WHERE EXISTS(SELECT COUNT(*)
FROM EMPLOYEE
WHERE E.EMPID = EMPID AND E.MONTH >= MONTH
GROUP BY EMPID, MONTH
HAVING COUNT(*) = 3)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 24 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.