1

I am trying to fetch the latest group of data based on given id from a table.

Here ..what I have tried..

SELECT * 
FROM service_provider_portfolio_photo 
where service_provider_portfolio_detail_id IN (2,3) order by created_on desc

OUTPUT:

Got all the list which have id (2,3).But I need to get the top first data from each id.Is this possible?

cdaiga
  • 4,861
  • 3
  • 22
  • 42
VASIS
  • 33
  • 8

1 Answers1

2

you could use a inner join on max created_on 

SELECT * 
FROM service_provider_portfolio_photo  p 
inner join (
    select service_provider_portfolio_detail_id, max(created_on) max_date
    from service_provider_portfolio_photo
    where service_provider_portfolio_detail_id IN (2,3) 
    group by service_provider_portfolio_detail_id 
) t  on t.service_provider_portfolio_detail_id = p.service_provider_portfolio_detail_id
          and t.max_date = p.created_on
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thank you.Its working fine :) ..But i am getting 2 data from each id since I have duplicate created time. Here I need to get the top first data for each id .Any suggestion? – VASIS Apr 10 '18 at 09:53
  • you mean that haev tow rows with the same created_on in service_provider_portfolio_photo table? – ScaisEdge Apr 10 '18 at 11:08
  • Thnk you. I got it – VASIS Apr 11 '18 at 05:51