Databases
StackOverflow2013
Postsdbo
POManaging latest versions of rows with group by queries and how to best arrange the primary key
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POManaging latest versions of rows with group by queries and how to best arrange the primary key
    primarykey
    data
    text
    <p>I have the following table</p> <pre><code>| Path | Version | FirstName | LastName | | People/Frank | 1 | Frank | Smith | | People/Frank | 2 | Frank | Jones | | People/Jack | 1 | Jack | Johnson | </code></pre> <p>I'd like my query to return the <code>Path</code> and <code>Max Version</code> for all the rows that match a given criteria. </p> <p>Currently I'm doing this;</p> <pre><code>select Path, MAX(Version) as Version from Table where FirstName = 'Frank' group by Path; </code></pre> <p>This is a really performance critical part of the code and I'm wondering if there's something specific I can do to sql server that would make this quicker or if there's something I'm missing.</p> <p>Additionally I'd like to make sure I have my constraints defined correctly. I'm expecting the queries to contain any or all of the columns that aren't path and version, so you could in the above case query for either FirstName, LastName or both. My create table sql looks like this:</p> <pre><code>create table Index_PersonByFirstName( FirstName NVarChar(100) not null, LastName NVarChar(100) not null, Path NVarChar(100) not null, Version Int not null, constraint pk_Index_PersonByFirstName primary key( FirstName, LastName, Path, Version), constraint uc_Index_PersonByFirstName_Path_Version unique ( Path, Version), constraint fk_People_Path_Version foreign key ( Path, Version) REFERENCES People(Path, Version)) </code></pre> <p>Would it make sense to remove the <code>Path</code> from the primary key as that's never directly queried?</p> <p>Another option I've considered is having a column that indicates if the row is the 'latest' version for a given path and updating the old rows when a new one is written, but that feels icky.</p> <p>Your thoughts would be greatly appreciated. If I haven't been detailed enough please let me know and I'll add any other information that is required.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USjonnii
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload