Query column from Distinct Query 1

[tqeonline]

Hey guys.. I think i have an ID 10 T error on this one.

SELECT distinct [Location],[Environment], [Page], [Action], [Type], [Status]
            FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] 
            WHERE [DateTime] >= DATEADD(minute,-15,GETDATE())

I need to remove [status] and query it after the distinct on the first five columns. So basically i need something like:

SELECT distinct [Location],[Environment], [Page], [Action], [Type], *Inner query that joins Status... based off of the distincts*
            FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] 
            WHERE [DateTime] >= DATEADD(minute,-15,GETDATE())

 

[tqeonline]

As far as Data goes... it looks like this:

Location Environment Page Type Status
Nashville QA 03 Login GUI 3
Nashville QA 03 Login GUI 2
Nashville QA 03 Login GUI 1

And what i wanted it to do is... lets say that the one with status 2 is the most recent... Then grab the most recent distinct row (the one with status 2 in this instance)... But you cant use distinct with Status being included because it returns all three.

 

[markros]

Take a look at these blog posts to understand solution for this problem


Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[tqeonline]

I dont understand how those tie in with my issue. If i just want a distinct with an added column of data... IS my issue bigger than what im thinking?

 

[markros]

In your query, which field dictates the most recent record?

;with cte as (select Location, Environment, Page, [Type],[Status], row_number() over (partition by Location,
Environment, [Page],[Type] order by [DateField] DESC) as Row from myTable)

select * from cte where Row = 1

This and other solutions described in that blog in details.

PluralSight Learning Library

 

[tqeonline]

You could determine it by the field called [DateTime]... i didn't include it because it doesn't help my distinct.

 

[tqeonline]

There is also an identifier field ID that just increments.

[ID]

 

[tqeonline]

This is what I got running in the test environment:

WITH cte AS 
	            (select [Location], [Environment], [Page], [Type], [Status], row_number() 
	            OVER (partition by [Location], [Environment], [Page], [Type] 
	            ORDER BY [DateTime] DESC) as Row 
            FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG])
            SELECT * 
            FROM cte 
            WHERE Row = 1
            AND [DateTime] >= DATEADD(minute,-15,GETDATE()) 
            AND [Location] LIKE @Location
            AND [Environment] LIKE @Environment 
            AND [Client] LIKE @Client
            ORDER BY [Status]
[code/]

 

[markros]

This is not correct - you need to add all your WHERE clause criteria to the CTE, not after the fact.

PluralSight Learning Library

 

[tqeonline]

Why does it matter where the Where clause is? Wouldn't it do the same thing on either situation?

 

[tqeonline]

I updated it to this:

WITH cte AS 
                (select [Location], [Environment], [Page], [Type], [Client], [Status], [DateTime], [Action], row_number() 
                OVER (partition by [Location], [Environment], [Page], [Type], [Client], [Action] 
                ORDER BY [DateTime] DESC) as Row 
                FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG]
                WHERE [DateTime] >= DATEADD(minute,-15,GETDATE())
                AND [Location] LIKE @Location
                AND [Environment] LIKE @Environment 
                AND [Client] LIKE @Client)
            SELECT * 
            FROM cte 
            WHERE Row = 1
            ORDER BY [Status]

 

[tqeonline]

So why putting the where clause where we just did... the CTE temporary table holds less rows... and it seems to be instead of .5 seconds more like .25 seconds seemed like just a screen refresh when i ran query instead of a slow page load.

intersting.

Thanks!

 

[markros]

We need to apply filtering and row_number() together. Otherwise the row_number will apply to the whole table and the final result may be incorrect.

PluralSight Learning Library