[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • Last Modified:

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


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
LuckyLucks
Asked:
LuckyLucks
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now