Query required

Guru Nandu · New User Joined: 16 Jul 2009 Posts: 6 Location: India

Hi Team,

I have a table that contains Name, Date of access, salary and Rate

Lets say table contains below values

Name Date of access Salary Rate
ABC 2014-02-13 111 0.5
CDE 2010-01-11 222 0.1
ABC 2010-01-01 333 0.2
ZZZ 2009-01-06 444 0.6
XXX 2014-01-30 555 0.1
CDE 2013-12-12 666 0.1

I want the output which select rows of latest date for a given name.
Output expected will be

Name Date of access Salary Rate
ABC 2014-02-13 111 0.5
ZZZ 2009-01-06 444 0.6
XXX 2014-01-30 555 0.1
CDE 2013-12-12 666 0.1

Help required in writing this DB2 query

TheMFKid · New User Joined: 20 Nov 2013 Posts: 91 Location: India

post the query you have been trying and we will help you to get it right.

Guru Nandu · New User Joined: 16 Jul 2009 Posts: 6 Location: India

SELECT Name FROM Table where
Name = (SELECT Name FROM Table WHERE
Date of access = (SELECT MAX(Date of access ) FROM Table))

sureshpathi10 · Posted: Fri Feb 14, 2014 3:39 pm

Can you try with this?

Guru Nandu · New User Joined: 16 Jul 2009 Posts: 6 Location: India

Hi Suresh,

It works with the query that you provided if i retriece name and Date of access.

However I require rate along with Name and date of access.

SELECT Name, Rate, MAX(Date of access)
FROM Table
GROUP BY Name, Rate
WITH UR;

I am getting duplicate rows having same name ABC.

sureshpathi10 · Posted: Mon Feb 17, 2014 8:05 am

I'm guessing, Name and Date of access will be unique.

Here is the query.