select rows in sql with latest date from 3 tables in each group

Question

I'm creating PREDICATE system for my application.

Please see image that I already

I have a question how can I select rows in SQL with latest date "Taken On" column tables for each "QuizESId" columns, before that I am understand how to select it but it only using one table, I learn from this select rows in sql with latest date for each ID repeated multiple times

Here is what I have already tried

SELECT tt.*
FROM myTable tt
INNER JOIN 
    (SELECT ID, MAX(Date) AS MaxDateTime
     FROM myTable
     GROUP BY ID) groupedtt ON tt.ID = groupedtt.ID 
                            AND tt.Date = groupedtt.MaxDateTime

What I am confused about here is how can I select from 3 tables, I hope you can guide me, of course I need a solution with good query and efficient performance.

Thanks

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Mar 18 '20 at 05:09
@marc_s thanks for you advice, I'm aware that using old style is not readable, but how about performance, is it same as join? — umar alfath, Mar 18 '20 at 05:32
thanks @marc_s, it;s really nice guidement, i get much from this. back to my problem can you give answer for my question — umar alfath, Mar 18 '20 at 05:36
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) — , Mar 18 '20 at 05:53

marc_s · Accepted Answer · 2020-03-18T08:28:56.883

1

This is for SQL Server (you didn't specify exactly what RDBMS you're using):

if you want to get the "latest row for each QuizId" - this sounds like you need a CTE (Common Table Expression) with a ROW_NUMBER() value - something like this (updated: you obviously want to "partition" not just by QuizId, but also by UserName):

WITH BaseData AS
(
    SELECT 
        mAttempt.Id AS Id,
        mAttempt.QuizModelId AS QuizId,
        mAttempt.StartedAt AS StartsOn,
        mUser.UserName,
        mDetail.Score AS Score,
        RowNum = ROW_NUMBER() OVER (PARTITION BY mAttempt.QuizModelId, mUser.UserName
                                    ORDER BY mAttempt.TakenOn DESC)
    FROM 
        UserQuizAttemptModels mAttempt
    INNER JOIN 
        AspNetUsers mUser ON mAttempt.UserId = muser.Id
    INNER JOIN
        QuizAttemptDetailModels mDetail ON mDetail.UserQuizAttemptModelId = mAttempt.Id
)
SELECT *
FROM BaseData
WHERE QuizId = 10053  
  AND RowNum = 1

The BaseData CTE basically selects the data (as you did) - but it also adds a ROW_NUMBER() column. This will "partition" your data into groups of data - based on the QuizModelId - and it will number all the rows inside each data group, starting at 1, and ordered by the second condition - the ORDER BY clause. You said you want to order by "Taken On" date - but there's no such date visible in your query - so I just guessed it might be on the UserQuizAttemptModels table - change and adapt as needed.

Now you can select from that CTE with your original WHERE condition - and you specify, that you want only the first row for each data group (for each "QuizId") - the one with the most recent "Taken On" date value.

edited Mar 18 '20 at 08:28

answered Mar 18 '20 at 05:46

marc_s

732,580
175
1,330
1,459

I learned the fundamentals of DB Design from [Database Design fo Mere Mortals](https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0321884493/ref=sr_1_1?keywords=database+design+mere+mortals&qid=1584512323&sr=8-1) by Mike Hernandez. For performance tuning I highly recommend [SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance](https://www.amazon.com/Server-2017-Query-Performance-Tuning/dp/1484238877/ref=sr_1_1?crid=15QJE9BKOF7JS&keywords=sql+server+performance+tuning&qid=1584512381&sprefix=sql+server+performance%2Caps%2C242&sr=8-1) by Grant Fritchey. – marc_s Mar 18 '20 at 06:20
This query will perform well **if** you have the proper indices in place - any foreign key column should be indexed, and if you have a `WHERE` clause, that column (or those columns) should also be indexed if possible. But it will also depend quite a bit on your data - how much, what's the "shape" of the data. Performance is a very complex topic - and not easy to deal with unless you can actually work with the concrete data.... – marc_s Mar 18 '20 at 06:22
Not really sure I understand what your problem is.. maybe your definition of what makes up the "data group/partition" is not complete? Maybe you need to use `PARTITION BY QuizModelId, mUser.UserName` instead? (if I correctly understood what you're trying to do....) – marc_s Mar 18 '20 at 07:30
oh yes thanks for your book advice, i will buy that and learn, i hope can be expert like you – umar alfath Mar 18 '20 at 07:36
As I said - you need to **partition by** not just the QuizId, but also the "Name" - try this: `ROW_NUMBER() OVER (PARTITION BY mAttempt.QuizModelId, mUser.UserName` - does that return the correct results? – marc_s Mar 18 '20 at 08:27
@marc_s . . . Nothing in this answer looks specific to SQL Server. It looks like standard SQL to me. – Gordon Linoff Mar 18 '20 at 12:18
@GordonLinoff: I'm only really "fluent" in SQL Server / T-SQL and that's what I used for this. I'm not knowledgeable enough how much of this syntax (CTE, windowing function) other RDBMS support - but I'll take your word for it ;-) – marc_s Mar 18 '20 at 15:18

select rows in sql with latest date from 3 tables in each group

1 Answers1