|
View previous topic :: View next topic
|
| Author |
Message |
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
This is the table :
| Code: |
Dept Section Class
=== ===== ====
1 A X
1 A Y
1 B X
2 A X
2 B X
3 A X
3 A Y
3 A Z |
I want to fetch all the coloumns for the first records of distinct Dept.
So the O/P will be:
| Code: |
Dept Section Class
=== ===== ====
1 A X
2 A X
3 A X |
Please help me out. |
|
| Back to top |
|
 |
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| What have you tried so far? |
|
| Back to top |
|
|
km_abdullah
New User
Joined: 03 Nov 2008
Posts: 60
|
|
|
|
Hi debasis misra,
You may have to add a unique column to the table say 'col1' which should be in ascending order and can achieve your requirement. Try below -
| Code: |
SELECT * FROM TABLE1 WHERE col1 IN (SELECT MIN(col1)
FROM TABLE1 GROUP BY DEPT); |
|
|
| Back to top |
|
|
DB2 Guy
New User
Joined: 28 Oct 2008
Posts: 98
Location: Cubicle
|
|
|
|
| How about SELECT DISTINCT? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| DB2 Guy wrote: |
| How about SELECT DISTINCT? |
The OP needs all the three columns in output.
| Debasis Misra wrote: |
| I want to fetch all the coloumns for the first records of distinct Dept. |
By first record, if you meant the 'first' Class of the 'first' Section in ascending alphabetical order , how about trying this?
| Code: |
SELECT DEPT
,MIN(SECTION)
,MIN(CLASS)
FROM TABLE1
GROUP BY DEPT
|
|
|
| Back to top |
|
|
km_abdullah
New User
Joined: 03 Nov 2008
Posts: 60
|
|
|
|
Hello Arun,
Is there a way to add a dummy column to the table instead of altering table with the column containing values in ascending order, thereby we can fetch the 'first' record of each distinct DEPT using MIN function & GROUP BY class? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Mohamed Abdullah
I dont think there's a "first" or "last" or an "nth record" for a DB2 table. |
|
| Back to top |
|
|
km_abdullah
New User
Joined: 03 Nov 2008
Posts: 60
|
|
|
|
Hi Arun,
I was referring to this Topics's requirement of fetching first of the distinct records of DEPT.
When we add a dummy column say 'column1' containing values from 1 to 8 the table would like below -
| Code: |
Dept Section Class column1
=== ===== ==== =====
1 A X 1
1 A Y 2
1 B X 3
2 A X 4
2 B X 5
3 A X 6
3 A Y 7
3 A Z 8 |
After this we can use MIN & GROUP BY as below to get our requirement.
| Code: |
SELECT * FROM TABLE1 WHERE column1 IN (SELECT MIN(column1)
FROM TABLE1 GROUP BY DEPT); |
Is it possible to add such dummy column without altering the Table. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Mohamed Abdullah,
Evenif the OP mentioned that he needs the "first records of distinct Depts", I believe it's not exactly doable as the concept is entirely different when it comes to a DB2 table. And you might end up with different results for the same sql. |
|
| Back to top |
|
|
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
Thanks Arun!!!!!!!
your concept of using MIN function is served my purpose....
really thanks a lot!!!!!!!!!!!! |
|
| Back to top |
|
|
|
|
