Solved

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

Posted on 2009-04-09
1
689 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
1 Comment
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
Comment Utility

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now