0

I have this table:

id_category | id_service | amount | date

This table have more than one rows with same id_category and id_service. How I can get only row from same id_category and id_service with the max date of them?

Example data:

1 | 1 | 0.1  | 2015-05-05
1 | 1 | 0.12 | 2015-05-06
1 | 2 | 0.2  | 2015-05-04
1 | 2 | 0.25 | 2015-05-05
1 | 2 | 0.30 | 2015-05-06
2 | 1 | 0.15 | 2015-05-05

I want to get this results:

1 | 1 | 0.12 | 2015-05-06
1 | 2 | 0.30 | 2015-05-06
2 | 1 | 0.15 | 2015-05-05

Thanks!

Hernán Gabriel
  • 37
  • 10
  • 2
    You included the solution in your question's tags: `GROUP BY`. Provide the query you have tried and we can work out evetual problems together. – Eggplant May 06 '15 at 16:52
  • 1
    Anyways, look at the first *related question* listed right here on the right: http://stackoverflow.com/questions/612231 – Eggplant May 06 '15 at 17:02

4 Answers4

1

http://sqlfiddle.com/#!9/ad96b/3

SELECT t1.* 
FROM t1
LEFT JOIN t1 t2
ON t1.id_category = t2.id_category
   AND t1.id_service = t2.id_service
   AND t1.`date` < t2.`date`
WHERE t2.date IS NULL
Alex
  • 16,739
  • 1
  • 28
  • 51
0

Maybe your query like this

select a.* from table a
where a.date = 
(select max(b.date) from table b where a.id=b.id group by b.id_service, b.id_category)
group by a.id_category, a.id_service;
Akim
  • 136
  • 1
  • 9
0

You can use derived table then use inner join

MYSQL Version SQL FIDDLE

SELECT gd.*, t.amount
FROM (Select id_category, id_service, max(date) date
      FROM t
      group by id_category, id_service)  gd
INNER JOIN t ON gd.id_service = t.id_service 
                AND gd.id_category = t.id_category
                AND gd.date = t.date
ORDER BY gd.id_category, gd.id_service

MSSQL Version:- This will be get with CTE if SQL Server SQLFIDDLE

;WITH GroupedData AS
   (
       select id_category, id_service, max(date) date
       FROM t
       group by id_category, id_service       
   ) 
   SELECT gd.*, t.amount
   FROM GroupedData  gd
   INNER JOIN t ON gd.id_service = t.id_service 
                AND gd.id_category = t.id_category
                AND gd.date = t.date
   ORDER BY gd.id_category, gd.id_service
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
-1
select id_category, id_service, max(amount), min(date)
from table_name
group by id_category, id_service;
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Swati Joshi
  • 51
  • 1
  • 8
  • this will not work if max amount is bigger for other lower date – HaveNoDisplayName May 06 '15 at 17:11
  • @Piyush I have tried that out and it works even if max amount is bigger for other lower date. – Swati Joshi May 06 '15 at 17:24
  • that works only current sample data, currently OP has sample data where max(amount) is always max where max(date), just change sample data for first sample row to this `1 | 1 | 0.15 | 2015-05-05`. see this http://sqlfiddle.com/#!6/04498f/3....here it gives wrong amount field – HaveNoDisplayName May 06 '15 at 17:27
  • I am afraid that I am still not agree with you. I tried changing the values and yet I am getting the expected results. Anyways Hernán got the answer I guess :) – Swati Joshi May 06 '15 at 18:00