|
View previous topic :: View next topic
|
| Author |
Message |
santhosh.kumar.vj
New User
Joined: 03 Aug 2011
Posts: 8
Location: Bangalore
|
|
|
|
hi folks
I have a tables in this structure
SELECT * FROM X1;
---------+---------+---------+---------+---------+---------+---------+---------+
ENO ENAME ESAL DEPTNO MOBILE
---------+---------+---------+---------+---------+---------+---------+---------+
1. MADHAN........40000....777.........9876543212.
2. VINOTH.........40000....888.........8886543212.
3. ARAVIND.......40000....777.........7777743212.
4. XXX..............85000.....777.........7777743212.
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
I want the output like this
---------+---------+---------+---------+---------+---------+---------+---------+
ENO ENAME ESAL DEPTNO MOBILE
---------+---------+---------+---------+---------+---------+---------+---------+
1. MADHAN........40000....777.........9876543212.
2. VINOTH.........40000....888.........8886543212.
I need a query to retrieve the first record in the duplicates considering the column DEPTNO.
Please help me to sort this problem.
Thanks in Advance |
|
| Back to top |
|
 |
xknight
Active User
Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
|
|
|
|
Hello,
| Quote: |
I want the output like this
---------+---------+---------+---------+---------+---------+---------+---------+
ENO ENAME ESAL DEPTNO MOBILE
---------+---------+---------+---------+---------+---------+---------+---------+
1. MADHAN........40000....777.........9876543212.
2. VINOTH.........40000....888.........8886543212. |
Your output displays two retrieved records, but you asked for a query to retrieve the first record in the duplicates
| Quote: |
| I need a query to retrieve the first record in the duplicates considering the column DEPTNO |
Did you tried
| Code: |
| FETCH FIRST ROW ONLY |
Hope it helps |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I'm really curious how xknight is solving this with fetch first row only.
I would solve it this way:
| Code: |
select * FROM X1 A
where A.eno = (select min(b.eno) FROM X1 b where a.deptno = b.deptno) |
|
|
| Back to top |
|
|
shibub
New User
Joined: 16 Oct 2009
Posts: 13
Location: Bangalore
|
|
|
|
A cursor which has a "OPTIMIZE FOR 2 ROWS" will get the first 2 rows matching the requirement.
e.g.
| Code: |
EXEC SQL DECLARE C-S CURSOR FOR
SELECT SEQ
FROM MYNAM WHERE NAME=:WS-NAME
OPTIMIZE FOR 2 ROWS
END-EXEC.
|
-Shibu.T- |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi shibubalakrishnan
Please do not provide the wrong information..
OPTIMIZE FOR 2 ROWS is not used for getting two rows which matches the requirement..
Check manuals for use of OPTIMIZE clause.
Regards,
Chandan |
|
| Back to top |
|
|
shibub
New User
Joined: 16 Oct 2009
Posts: 13
Location: Bangalore
|
|
|
|
| Quote: |
| Please do not provide the wrong information.. |
| Quote: |
| Check manuals for use of OPTIMIZE clause. |
The OPTIMIZE FOR clause requests special optimization of the select-statement. The clause specifies that optimization is based on the assumption that the number of rows retrieved will not exceed n, where n is the value of the integer. If the clause is omitted, optimization is based on the assumption that all rows of the result table will be retrieved unless the FETCH FIRST clause is specified. If OPTIMIZE FOR is omitted and FETCH FIRST is specified, OPTIMIZE FOR integer ROWS is assumed, where integer is the value of FETCH FIRST.
The OPTIMIZE FOR clause does not limit the number of rows that can be fetched or affect the result in any way other than performance. In general, if you are retrieving only a few rows, use OPTIMIZE FOR 1 ROW to influence the access path that DB2 selects. For more information about using this clause, see DB2 Application Programming and SQL Guide.
I strongly believe that we can fetch 2 rows with a "OPTIMIZE for 2 rows"
-Shibu.T- |
|
| Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
|
|
|
|
Hello,
| Quote: |
| I'm really curious how xknight is solving this with fetch first row only. |
Mis-understood the TS query i suppose 
Thanks for pointing it out. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| shibubalakrishnan wrote: |
| Quote: |
| The OPTIMIZE FOR clause does not limit the number of rows that can be fetched or affect the result in any way other than performance. |
I strongly believe that we can fetch 2 rows with a "OPTIMIZE for 2 rows"
|
you can fetch 2000 rows with an "optimize for 2 rows" but it will never remove rows with identical deptno. |
|
| Back to top |
|
|
shibub
New User
Joined: 16 Oct 2009
Posts: 13
Location: Bangalore
|
|
|
|
| ah. I'm sorry, I thought the 40000 is the key |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| shibubalakrishnan wrote: |
| ah. I'm sorry, I thought the 40000 is the key |
I think you still don't get it. Which field is the key is irrelevant. |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi,
To my knowledge, there is no first row or last row as far as DB2 is concerned. If my knowledge is not correct please correct me.
For the above query, GROUP BY can be used to get a single row from the DEPT column. But it is not guaranteed that the row in the result set will satisfy your condition. To get the desired result, more filter is required(ENO, ESAL etc).
Regards
Raghu |
|
| Back to top |
|
|
santhosh.kumar.vj
New User
Joined: 03 Aug 2011
Posts: 8
Location: Bangalore
|
|
|
|
| Thanks guyc for ur inputs,the query is working fine |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Raghu navaikulam wrote: |
Hi,
To my knowledge, there is no first row or last row as far as DB2 is concerned. If my knowledge is not correct please correct me.
For the above query, GROUP BY can be used to get a single row from the DEPT column. But it is not guaranteed that the row in the result set will satisfy your condition. To get the desired result, more filter is required(ENO, ESAL etc).
Regards
Raghu |
True, there is no sequence in a db2 table. but I assumed he wants the row with the lowest primary key, ie. ENO.
a group by is not possible if you want more columns than only DEPTNO. |
|
| Back to top |
|
|
|
|
