In a sorted group of records, show only top record by PK ID

Rx_

Nothing In Moderation
Local time
Yesterday, 18:14
Joined
Oct 22, 2009
Messages
2,803
How to create a view (query) that sorts on [date Entered], then only shows the single top record (e.g the last date that a record was entered).

Created a view that shows records sorted in order of the [Date Submitted] so it sorts with the latest date on the top.

CustomerID-----Date Entered--------- Other criteria
PK Sorted Asc --Latest date sort ------filters on other fields

The query has 0 to many records for each Customer ID. The [Date Entered] is always on top.

Now, with the list above, My final View will only show the 1st record (the latest Date Entered).

Groupby such as the First, Max Top, .... don't seem to work.
 
Use analytics, google "Partition by" to find out details....
Code:
Select * 
From (
Select ID
     , YourDate
     , ROW_NUMBER( ) OVER (PARTITION BY ID ORDER BY YourDate desc) LineNo
From yourTable
)
Where LIneNo = 1

Analytics are a very powerfull tool for a lot of things SQL, like running sums and such as well as this kind of thing....

Alternatively you can use an old fashioned subselect / Self join
Code:
Select Yourtable.*
from Yourtable 
inner join (Select ID
                 , Max(Yourdate) maxYourDate 
            from yourtable 
            Group by ID) as x
                          On Yourtable.id = x.id
                         and Yourtable.Yourdate = x.MaxYourDate)
 
Thanks! Was just looking at that at closing time.
this method to select using the new window function ROW_NUMBER() introduced in SQL 2005. I like that it simply creates a numbering system that enumerates each instance of the chosen ID ordered by the (in my case) last date column.
That select statement is then called by the outer select.
That isn't something we can do from within Access. What a bonus!
 
See Attachments!
This is very powerful and very fast on SQL Server.
Posting a couple of results that should help others. After reading several articles I just couldn't get it exactly right. Your example helped fill in the missing link. Thanks!

The Part 1 attachment shows how to take the Row_Number and Partition By to set up and test the basic concept. The latest date action RE_24DTSub indicates what happened last by Well_ID.
This helps prove the Acends are in the proper order.

Part 2 - take the code from part 1, modify it and fold it into another Select statement.

The yellow highlights prove the first record is the one needed for my case.

The table called in both of these are actually a View. My next step is to fold this into a single view.

One of the problems previously noted was how a SQL server view order didn't necessarly show up the same on the Access Linked Table side when using SQL Server Native Client 11. The basic view was being brought down to a linked table in Access, where it was resorted again, then a temp table was created to harvest the records showing rownumber 1 above. Now, my prevoius 12 process can be redone using this method on SQL Server.

If anyone else finds this detailed post useful, be sure to give Namlian a thanks. :D
 

Attachments

  • Row_Number Partition By part 1.jpg
    Row_Number Partition By part 1.jpg
    84.9 KB · Views: 170
  • Row_Number Partition By part 2.jpg
    Row_Number Partition By part 2.jpg
    86 KB · Views: 160
Last edited:
a linked table is nothing but a select statement on your view.
even with the view being ordered, any select on top of that -without explicit order by- will not have any guaranteed order... ONLY if you add an explicit order by clause can you expect any set order in anything that views data for you (that includes tables,queries, forms and reports)

I am glad you got it figured out :) Analytics are indeed a VERY powerfull thing, I dont nearly use it enough to be great at it... But I get by and do regularly get requests about it from around my business area even from peer developers who dont seem to be able to grasp the concept.
 
Just a follow up on the use of my View using the top Row Number after it is Partition By

First, I have a View created that list selected information.
In this attachment each view has different criteria in the 2nd Column.
e.g. Fed, State, County, City, Tribal, Rail Road, ...
These views are important as they list all transactions in Date order for Each ID_Wells. For some parts of the program, all the details are necessary.
The Attachment shows the use of the Over (Partition By) clause.
It consumes each View resulting in the TOP 1 (in this case the record for each ID_Wells latest date).
It always returns a Null or Single Record.

It becomes a View with the same name plus TOP.
Important to note, this TSQL statement can not be shown in the grid or diagram views.

The advantage of this style is:
1. There is a view for either all transactions or just the top transaction.
2. If the base view changes (paying attention not to change the two key fields hightlighted in yellow) it is refected in the TOP version. For example adding a couple of extra fields at the end. This can reduce maintenance.
3. Using linked tables for the TOP, they return null or 1 record.
This means that an outer join of a table with ID_Wells to multiple tables with TOP will remain at 1 record for each ID_Wells.
4. Regarding TOP, All of the horsepower takes place on the SQL Server engine using TSQL. This is very efficient. (see attachment 3)

For anyone asking what is the advantage of moving the Access DB over to SQL Server: this is very powerful if the project requires more complex data analysis.
 

Attachments

  • Row_Number Partition By part 3.jpg
    Row_Number Partition By part 3.jpg
    87.2 KB · Views: 165
  • Row_Number Partition By part 4.jpg
    Row_Number Partition By part 4.jpg
    59.9 KB · Views: 158
Last edited:

Users who are viewing this thread

Back
Top Bottom