SQL selecting rows by most recent date with two unique columns

Question

Using the following query and results  I m looking for the most recent entry where the ChargeId and ChargeType are unique   select chargeId  chargeType  serviceMonth from invoice      CHARGEID    CHARGETYPE  SERVICEMONTH 1   101     R       8 1 2008 2   161     N       2 1 2008 3   101     R       2 1 2008 4   101     R       3 1 2008 5   101     R       4 1 2008 6   101     R       5 1 2008 7   101     R       6 1 2008 8   101     R       7 1 2008   Desired       CHARGEID    CHARGETYPE  SERVICEMONTH 1   101     R       8 1 2008 2   161     N       2 1 2008

User · Accepted Answer

You can use a GROUP BY to group items by type and id   Then you can use the MAX   Aggregate function to get the most recent service month   The below returns a result set with ChargeId  ChargeType  and MostRecentServiceMonth  SELECT   CHARGEID    CHARGETYPE    MAX SERVICEMONTH  AS  MostRecentServiceMonth  FROM INVOICE GROUP BY CHARGEID  CHARGETYPE

User · Answer

SELECT chargeId  chargeType  MAX serviceMonth  AS serviceMonth  FROM invoice GROUP BY chargeId  chargeType

User · Answer

SELECT chargeId  chargeType  MAX serviceMonth  AS serviceMonth  FROM invoice GROUP BY chargeId  chargeType

User · Answer

I see most of the developers use inline query without looking out it s impact on huge data   in simple you can achieve this by   select a chargeId  a chargeType  a serviceMonth  from invoice a left outer join invoice b on a chargeId b chargeId and a serviceMonth  lt b serviceMonth  where b chargeId is null order by a serviceMonth desc

User · Answer

SELECT chargeId  chargeType  MAX serviceMonth  AS serviceMonth  FROM invoice GROUP BY chargeId  chargeType

User · Answer

select to chargeid t0 po i chargetype from invoice i inner join  select chargeid max servicemonth po from invoice  group by chargeid t0 on i chargeid t0 chargeid   The above query will work if the distinct charge id has different chargetype combinations Hope this simple query helps with little performance time into consideration

User · Answer

So this isn t what the requester was asking for but it is the answer to  SQL selecting rows by most recent date    Modified from http   wiki lessthandot com index php Returning The Maximum Value For A Row  SELECT t chargeId  t chargeType  t serviceMonth FROM       SELECT chargeId MAX serviceMonth  AS serviceMonth     FROM invoice     GROUP BY chargeId  x      JOIN invoice t ON x chargeId  t chargeId     AND x serviceMonth   t serviceMonth

User · Answer

I see most of the developers use inline query without looking out it s impact on huge data   in simple you can achieve this by   select a chargeId  a chargeType  a serviceMonth  from invoice a left outer join invoice b on a chargeId b chargeId and a serviceMonth  lt b serviceMonth  where b chargeId is null order by a serviceMonth desc

User · Answer

SELECT chargeId  chargeType  MAX serviceMonth  AS serviceMonth  FROM invoice GROUP BY chargeId  chargeType

User · Answer

So this isn t what the requester was asking for but it is the answer to  SQL selecting rows by most recent date    Modified from http   wiki lessthandot com index php Returning The Maximum Value For A Row  SELECT t chargeId  t chargeType  t serviceMonth FROM       SELECT chargeId MAX serviceMonth  AS serviceMonth     FROM invoice     GROUP BY chargeId  x      JOIN invoice t ON x chargeId  t chargeId     AND x serviceMonth   t serviceMonth

User · Answer

select to chargeid t0 po i chargetype from invoice i inner join  select chargeid max servicemonth po from invoice  group by chargeid t0 on i chargeid t0 chargeid   The above query will work if the distinct charge id has different chargetype combinations Hope this simple query helps with little performance time into consideration

User · Answer

So this isn t what the requester was asking for but it is the answer to  SQL selecting rows by most recent date    Modified from http   wiki lessthandot com index php Returning The Maximum Value For A Row  SELECT t chargeId  t chargeType  t serviceMonth FROM       SELECT chargeId MAX serviceMonth  AS serviceMonth     FROM invoice     GROUP BY chargeId  x      JOIN invoice t ON x chargeId  t chargeId     AND x serviceMonth   t serviceMonth

User · Answer

So this isn t what the requester was asking for but it is the answer to  SQL selecting rows by most recent date    Modified from http   wiki lessthandot com index php Returning The Maximum Value For A Row  SELECT t chargeId  t chargeType  t serviceMonth FROM       SELECT chargeId MAX serviceMonth  AS serviceMonth     FROM invoice     GROUP BY chargeId  x      JOIN invoice t ON x chargeId  t chargeId     AND x serviceMonth   t serviceMonth

[sql] SQL selecting rows by most recent date with two unique columns

Examples related to sql

Examples related to oracle