Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need SQL for two latest records having ABC value in column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Q5P418

New User


Joined: 10 May 2020
Posts: 5
Location: USA

PostPosted: Sun May 10, 2020 8:24 am    Post subject: Need SQL for two latest records having ABC value in column
Reply with quote

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
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2382
Location: Hampshire, UK

PostPosted: Sun May 10, 2020 3:08 pm    Post subject: Reply to: Need SQL for two latest records having ABC value in column
Reply with quote

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
View user's profile Send private message
Q5P418

New User


Joined: 10 May 2020
Posts: 5
Location: USA

PostPosted: Sun May 10, 2020 5:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 812
Location: Maryland

PostPosted: Sun May 10, 2020 6:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Q5P418

New User


Joined: 10 May 2020
Posts: 5
Location: USA

PostPosted: Sun May 10, 2020 6:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 812
Location: Maryland

PostPosted: Sun May 10, 2020 7:19 pm    Post subject:
Reply with quote

What exactly is your problem?
Not what is YOUR TASK, but rather WHAT IS THE ISSUE you’ve been faced?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2382
Location: Hampshire, UK

PostPosted: Sun May 10, 2020 8:19 pm    Post subject: Reply to: Need SQL for two latest records having ABC value in column
Reply with quote

I do not see why you have
Code:
AND B IN (...)

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2443
Location: NY,USA

PostPosted: Mon May 11, 2020 8:02 am    Post subject:
Reply with quote

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
View user's profile Send private message
Q5P418

New User


Joined: 10 May 2020
Posts: 5
Location: USA

PostPosted: Mon May 11, 2020 6:55 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 812
Location: Maryland

PostPosted: Mon May 11, 2020 7:49 pm    Post subject:
Reply with quote

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”???!!! 12.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2443
Location: NY,USA

PostPosted: Tue May 12, 2020 12:06 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts to Insert Rows from File1 to File 2 ... Arun_Tupili JCL & VSAM 1 Thu Aug 13, 2020 6:22 pm
No new posts Retrieve multiple records with metaco... Jose Juan J CA Products 0 Sat Jul 25, 2020 1:36 am
No new posts Problem while trying to manipulate re... amitc23 DFSORT/ICETOOL 3 Tue Jun 30, 2020 1:19 pm
No new posts Need to select all records belonging ... Q5P418 DB2 2 Mon Jun 22, 2020 11:34 pm
No new posts How to filter out certain records bas... manugeorge2004 JCL & VSAM 5 Wed Jun 17, 2020 10:05 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us