CODEWITHSUNDEEP
sql
Rounak
Rounak

Reputation: 181

Select from database SQL query

I want to select those symbol where FA_Event="Watch List Qualified" and FA_ID=MAX(FA_ID). That's mean: in this database MAX(FA_ID) of Symbol "A"=1 and MAX(FA_ID) of Symbol "B"=3. I have to check whether on this max values(1,3) event="Watch List Qualified" or not. As there is no "Watch List Qualified" in FA_ID=3, So the Answer will be only "A". 

 FA_ID   FA_SYmbol    FA_Event                FA_DATE      FA_COmment
 1       A            NULL                    NULL         NULL
 1       A            Watch List Qualified    05-Mar-12    NULL
 1       B            NULL                    NULL         NULL
 1       B            Watch List Qualified    05-Mar-12    NULL
 2       B            NULL                    NULL         NULL
 2       B            e7                      NULL         NULL
 2       B            e9                      NULL         NULL
 2       B            Watch List Qualified    05-Mar-12    NULL
 3       B            NULL                    NULL         NULL
 3       B            e2                      NULL         NULL

Upvotes: 0

Views: 122

Answers (2)

user359040
user359040

Reputation:

Assuming your RDBMS supports the OVER clause with the MAX function, try:

select * from 
(select m.*, max(FA_ID) over (partition by FA_SYmbol) max_ID
 from myTable m) sq
where FA_ID = max_ID and
      FA_Event = 'Watch List Qualified'

Upvotes: 0

Jake Feasel
Jake Feasel

Reputation: 16955

It looks like this is what you are looking for:

http://sqlfiddle.com/#!3/5ff70/9

select * from 
table1
  inner join (
      select fa_symbol, max(fa_id) as maxid
      from table1
      group by fa_symbol
    ) maxes on
  table1.fa_symbol = maxes.fa_symbol AND
  table1.fa_id = maxes.maxid
where 
fa_event = 'Watch List Qualified'

Upvotes: 1

Related Questions