tsql nesting subquery - group by last entry by week

[darkgen]

I have a table with with a number of records but the key columns are UTCDate, USER and APPName.

SELECT        *, DATEPART(week, utcDate) AS WEEKNO
FROM            (SELECT        *, row_number() OVER (partition BY user, AppName
                          ORDER BY utcDate DESC) AS rn
FROM            dbo.sometable) a
WHERE        a.rn = 1

This returns the last entry for each user for each AppName. However, I would like to return the last entry for each user for every week. Nesting another subquery is the part which is confusing me. Any pointers much appreciated.

TIA!

 

[Meeko]

I think you just need to add your DatePart into the Partition?

 

[darkgen]

I can't believe it was that obvious! there's me thinking I needed another subquery within the partition!

SELECT        *, DATEPART(week, utcDate) AS WEEKNO
FROM            (SELECT        *, row_number() OVER (partition BY user, AppName, DATEPART(week, utcDate)
                          ORDER BY utcDate DESC) AS rn
FROM            dbo.sometable) a
WHERE        a.rn = 1

 

[Meeko]

Glad you got it sorted. I meant to come back to my post and add a bit more detail but forgot all about it!

 

[AndyCr15]

I think I have a similar problem. I wont lie, I don't entirely understand the above, so I think I'm a little out of my depth... (You're using one query on a query you've already made)

So... if I have item, rating and time they were last rated (there are lots of ratings) in a database, I want the most recent rating from each item. I'm sure it's not that hard... maybe...? (Oh, and only ratings within the last 3 hours count, but I think I have that bit)

At present I have -

SELECT
   any_value(rating),
   item,
   any_value(time)
FROM
   `table`
WHERE
   TIME > date_add(now(), INTERVAL - 3 HOUR)
GROUP BY
   rating

This gets me one rating from the last 3 hours, but not the most recent.

 

[HungryHippos]

With SQL there is usually more than one way to skin a cat.

Does this kind of thing get you the most recent within the last 3 hours for each item? I've renamed the fields and table a bit for the example, because yours seemed to have dup field names, and the fields had brackets in which made it more confusing.

It should only show items where there is actually a result within the last 3 hours (INNER JOIN means only return results where the m subquery has one).

SELECT
    r.Rating,
    r.Item,
    r.Time
FROM
    Tbl_Ratings AS r
INNER JOIN
    (
        SELECT
        Rating,
        Item,
        MAX(Time)
        FROM Tbl_Ratings
        WHERE Time > DATEADD(h, -3, GETDATE())
        GROUP BY Item
    ) AS m
    ON m.Rating = r.Rating
    AND m.Item = r.Item
    AND m.Time = r.Time
WHERE
    r.Time > DATEADD(h, -3, GETDATE())
GROUP BY
    r.Item

 

[AndyCr15]

I realise now, I perhaps didn't give all the info, but you correctly assumed I have a table of items and a table of ratings for these items.

With SQL there is usually more than one way to skin a cat.

Yes, in the end I added a `current_rating` and `time` fields to the `items` table and updated that along with adding the 'review' to the `reviews` table. I will have a go with yours though as I would rather remove that and do it all programmatically. Thanks.