How to get the last record using group by

Question

My Query :-

SELECT
    p.*,
    b.brand_name 
FROM 
    portfolio p,
    branding_category b
WHERE 
    p.category = 'BRANDING' 
AND
    p.brand_category = b.id
AND 
    is_active = '1' 
GROUP BY 
    p.brand_category
ORDER BY p.id DESC
LIMIT 10

Suppose portfolio table has :-

id  category brand_category is_active   title

1    test     8              1           abc
2    test     7              1           pqr
3    test     8              1           xyz
4    test     7              1           ijk

And I want to show Output has :- That is, the last record in each group should be returned.

id  category brand_name is_active   title

3    test     Catalogs     1         xyz
4    test     Posters      1         ijk

Edit :-

branding_category

id  brand_name
8   Catalogs
7   Posters

i.e, Last row for each group. Please help me on this. I know it is there in stackoverflow Retrieving the last record in each group but I am not able to write for two table.

How are you defining "last" group? Your `id` values don't match your `title` value. Which field is from where? What's in `branding_category`? — Clockwork-Muse, Jul 12 '14 at 07:18
@Clockwork-Muse branding_category has id and brand_name, see my edit — user3831692, Jul 12 '14 at 07:23
In the absence of any aggregating functions, your use of GROUP BY is inappropriate. If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jul 12 '14 at 08:44
@Strawberry sorry but my question is clear and I describe what I want in output in my question — user3831692, Jul 12 '14 at 09:38
In which case, I'm sure someone will be along with the correct answer very shortly. Sit tight. — Strawberry, Jul 12 '14 at 10:04

score 0 · Accepted Answer · answered Jul 12 '14 at 10:04

Try this :-

SELECT p.*,b.brand_name
    FROM portfolio p
      INNER JOIN branding_category b ON p.brand_category = b.id
      INNER JOIN (
        SELECT MAX(id) MaxMsgIDForThread
        FROM portfolio
        WHERE is_active = '1'

        GROUP BY brand_category
      ) g ON p.id = g.MaxMsgIDForThread
    Order by p.id Desc
    LIMIT 10

Vinoth_S · Answer 2 · 2014-07-12T08:20:47.300

-2

Try this,

 select x.id,x.category,x.brand_name,x.is_active,x.title from (
select
 p.id,p.category,pc.brand_name,p.is_active,p.title,
  ROW_NUMBER()over ( ORDER BY p.id) as Rnk
from portfolio p inner join branding_category pc
on p.brand_category=pc.id
) x where Rnk >2

edited Jul 12 '14 at 08:20

answered Jul 12 '14 at 07:20

Vinoth_S

1,380
1
12
15

No. Besides the fact that MySQL doesn't have `ROW_NUMBER(...)` (or windowing functions in general), I'm not aware of any system that allows you to supply a value _during execution_ (sometimes not even as a parameter!) for a `TOP` (or related clause). And the fact that subtracting 2 like that is fundamentally wrong anyways... `TOP` without an `ORDER BY` is pointless (gets random-ish rows). – Clockwork-Muse Jul 12 '14 at 07:36
But in mysql it is throwing an error `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ORDER BY category) as Id,* from( SELECT category,brand_category,is_active,titl' at line 1 ` – user3831692 Jul 12 '14 at 09:35

How to get the last record using group by

2 Answers2