CODEWITHSUNDEEP
sqlsql-servert-sql
Bonk
Bonk

Reputation: 1949

groupby/merge to the latest record?

I have two tables:

Table1: Food_I_Have_In_Fridge:

Food  | Category
---------------
Apple | Fruit
Beef  | Meat
Tomato| Veggie
Pork  | Meat
Bacon | Meat
Orange| Fruit
Carrot| Veggie

Table2: Food_I_ate: 

Date | Food
------------
Mon  | Apple
Mon  | Beef
Tues | Pork
Thurs| Orange
Sat  | Tomato

I want to generate a list of last things I ate categorized by the food category. The output looks like:

Categ| Food
-----------
Meat | Pork
Fruit| Orange
Veggi| Tomato

I havn't been able to group & merge rows, any suggestions?

thanks!

Upvotes: 1

Views: 68

Answers (1)

mellamokb
mellamokb

Reputation: 56769

Assuming you really are using names of weekdays for your date column, here is a solution which will translate the weekdays to weekday indices and find the latest food by category:

;with FW as (
    select w.ix, f.category, a.food, a.date
    from
        [Food_I_ate] a
    inner join
        [Food_I_Have_In_Fridge] f on a.Food = f.Food
    inner join
    (
        select 0 as ix, 'Sun' as weekdayname union
        select 1, 'Mon' union
        select 2, 'Tues' union
        select 3, 'Wed' union
        select 4, 'Thurs' union
        select 5, 'Fri' union
        select 6, 'Sat'
     ) w on w.weekdayname = a.[Date]
)
select
    FW2.category,
    FW2.Food,
    FW2.Date
from
    (select FW.category, max(fw.ix) as maxix
     from FW group by FW.category) FW
inner join
    FW FW2 on FW2.ix = FW.maxix and FW2.category = FW.category

Sample Output:

CATEGORY    FOOD    DATE
Veggie      Tomato  Sat
Meat        Pork    Tues
Fruit       Orange  Thurs

Demo: http://www.sqlfiddle.com/#!3/4b92f/21

Upvotes: 2

Related Questions