?
Solved

SQL - max date records with one field values belonging to a list of values

Posted on 2009-04-09
1
Medium Priority
?
705 Views
Last Modified: 2012-08-13

Hi:
   I waanted to create a SQL that does the following things.

I have a Table1 with structure:
A B C D E
data in table T1 is:
-------
A B C D E
1 Buy c1  May1 2009  E1
1 Buy c2  May 3 2009 E2
2 Sell C1 Dec 1 2008   E3

I want my SQL to return this:
A B D
1 Buy May 3 2009
2 Sell Dev 1 2008
i.e.
B belongs in a list of values say Buy,Sell,Hold
D = max date record for A
A = key

How should my SQL look?
0
Comment
Question by:LuckyLucks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 24108023

How about this?

-- DaveSlash


select t1.a,
       t1.b,
       t1.d
from   Table1 t1
where  t1.b in ('Buy','Sell','Hold')
  and  t1.d = (select max(t2.d)
               from   Table1 t2
               where  t2.a = t1.a)

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question