|
View previous topic :: View next topic
|
| Author |
Message |
Q5P418
New User
Joined: 10 May 2020
Posts: 7
Location: USA
|
|
|
|
I have a DB2 table having one of the columns (A) which has either value ABC or XYZ.
I need output where the latest two records based on col C date have value A = ABC.
Sample Table
A B C
ABC John 8/8/2019
ABC John 8/1/2019
XYZ John 7/1/2019
ABC Mary 10/11/2019
XYZ Mary 10/1/2019
ABC Paul 6/6/2019
ABC Paul 6/20/2019
| Code: |
A B C
ABC John 8/8/2019
ABC John 8/1/2019
XYZ John 7/1/2019
ABC Mary 10/11/2019
XYZ Mary 10/1/2019
ABC Paul 6/6/2019
ABC Paul 6/20/2019
|
In this sample table, my output would be John and Paul records |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
Please use the code tags - available using the full editor -when posting data. I have done it for you this time, leaving your untagged data in place so that you can see the difference.
Please show the SQL that have you tried. |
|
| Back to top |
|
|
Q5P418
New User
Joined: 10 May 2020
Posts: 7
Location: USA
|
|
|
|
I have tried below SQL as of now:
SELECT B, C
FROM TABLE
WHERE A = 'ABC'
AND B IN ('John', 'Paul',...............'Mary')
GROUP BY B
ORDER BY C DESC
FETCH FIRST 2 ROWS ONLY
Please note - I have around 500 records in column B which i need to process. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Q5P418 wrote: |
I have tried below SQL as of now:
SELECT B, C
FROM TABLE
WHERE A = 'ABC'
AND B IN ('John', 'Paul',...............'Mary')
GROUP BY B
ORDER BY C DESC
FETCH FIRST 2 ROWS ONLY
Please note - I have around 500 records in column B which i need to process. |
1) did you hear about code tags?
2) what is your problem? (besides of your obligation to do your own task?) |
|
| Back to top |
|
|
Q5P418
New User
Joined: 10 May 2020
Posts: 7
Location: USA
|
|
|
|
I have tried below SQL as of now:
| Code: |
SELECT B, C
FROM TABLE
WHERE A = 'ABC'
AND B IN ('John', 'Paul',...............'Mary')
GROUP BY B
ORDER BY C DESC
FETCH FIRST 2 ROWS ONLY |
Please note - I have around 500 records in column B which i need to process. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
What exactly is your problem?
Not what is YOUR TASK, but rather WHAT IS THE ISSUE you’ve been faced? |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
I do not see why you have
What is this trying to achieve?
Your dates are not in a suitable format for sorting - you should use ccyymmdd format or at least make the dates the same length so 1/1/2019 should be 01/01/2019. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Code: |
Select temp1.A,temp1.B,temp1.C
From
(
SELECT A,B,C
FROM TABLE
WHERE A = 'ABC'
) temp1
ORDER BY temp1.c DESC
FETCH FIRST 2 ROWS ONLY |
|
|
| Back to top |
|
|
Q5P418
New User
Joined: 10 May 2020
Posts: 7
Location: USA
|
|
|
|
Rohit,
Thanks for your response!
I have to process around 500 records in column B.
So the last statement wont work as i expect more than 2 records in output. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Q5P418 wrote: |
Rohit,
Thanks for your response!
I have to process around 500 records in column B.
So the last statement wont work as i expect more than 2 records in output. |
How do you imagine “500 records in a column”???!!!  |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Q5P418 wrote: |
Rohit,
Thanks for your response!
I have to process around 500 records in column B.
So the last statement wont work as i expect more than 2 records in output. |
Re-read your original post. You mentioned you need first two rows of latest rows ( based on col C) when A= 'ABC', so this query will work as per the need and it does not matter if its 500 or 5M. It should work.
If you have changed your requirements then please post it as such.
The following query will take care of the duplicates per row.
| Code: |
Select temp1.A,temp1.B,temp1.C
From
(
SELECT DISTINCT A,B,C
FROM TABLE
WHERE A = 'ABC'
) temp1
ORDER BY temp1.c DESC
FETCH FIRST 2 ROWS ONLY |
|
|
| Back to top |
|
|
|
|
