DB2 Query problem

[sunny]

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

[Bob Stearns]

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

[Tonkuma]

>> 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.

[sunny]

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

[Mark 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.

[Peri]

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

[sunny]

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

[Serge Rielau]

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