Databases
StackOverflow2013
Postsdbo
POLooking for DB2 SQL query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLooking for DB2 SQL query
    primarykey
    data
    text
    <p>I am not very familiar with SQL and I hope some expert here can show me suitable and efficient query for what I want to achieve. I am using DB2 by the way.</p> <p>Below is a screenshot of a sample data. What I need is for a given year, select the record with distinct ID1+ID2+Name columns and maximum (most recent) effective date (in YYYYMMDD format, stored as integer), with the above year being in between YearFrom and YearTo range.</p> <p><img src="https://i.stack.imgur.com/WXTiA.jpg" alt="enter image description here"></p> <p>FOr anyone that cant see a screenshot:</p> <pre><code>NAME YearFrom YearTo ID1 ID2 EffDate item1 2002 2005 AB 10 20091201 item1 2009 2013 AB 10 20100301 item2 2001 2004 XX 20 20050103 item2 2002 2009 XX 20 20060710 item2 2007 2013 XX 20 20090912 item3 2005 2010 YY 30 20110304 </code></pre> <p>I hope I explained it well. For example if user is looking for available items in year 2011, item1 (with eff. date 20100301) and item 2 (with eff. date 20090912) will be returned.</p> <p>If someone is looking for items available in year 2008: item2 (with eff. date 20090912) and item 3 will be returned. Item 1 will not be returned in this case because the most recent record for item 1 has range of 2009-2013.</p> <p>I <em>think</em> I have the first part of the query right, but I dont know how to select the valid records from that results based on the year in one query.</p> <pre><code>select name,id1,id2,max(effdate) from [table] group by name,id1,id2 </code></pre> <p>Any help would be much appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZyga
    1. USZyga
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLooking for DB2 SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLooking for DB2 SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COScreenshot is not available.. Please provide the sample data for the given example ..
      singulars
      1. POLooking for DB2 SQL query
      1. USpratik garg
    2. COsorry, though the screenshot is visible. I have edited now to include the sample data.
      singulars
      1. POLooking for DB2 SQL query
      1. USZyga
    3. COWhat are the names of your tables? EDIT: Never mind I thought I was looking at 2 different tables.
      singulars
      1. POLooking for DB2 SQL query
      1. USJeff Orris
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload