10

You can use a window function such as the ranking function called ROW_NUMBER() to generate a numerical series within a grouping (PARTITION) of each Person ordered by the MeetingDate descending, like so: SELECT ID, Person, MeetingDate FROM ( SELECT ID, Person, MeetingDate, ROW_NUMBER() OVER (PARTITION BY Person ORDER BY ...


6

Use this syntax: CREATE TABLE table1 ( field1 varchar(25) CONSTRAINT [df_table1_field1] DEFAULT('a') ) Have a look at MS Docs about Specify Default Values for Columns, specifically this secion: Named CONSTRAINT (T-SQL) CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50);


4

SQL Server 2012 and later provide the function FIRST_VALUE that returns the first value in an ordered set of values. Use DISTINCT to return only one row for each partition (for each person). You can use ID as a tiebreaker. SELECT DISTINCT Person ,FIRST_VALUE(ID) OVER (PARTITION BY Person ORDER BY MeetingDate desc, ID desc) FROM MyTable


3

Since you mention "Row Id of the record" (not plural) I'm going to go out on a limb and say that a unique constraint may not exist and we would need to use some criteria to act as a "tie-breaker" to make the query deterministic - I'm working under the assumption that Id is set as the primary key1 of the table, and using it as a tie-...


1

You could get the MAX MeetingDate as you do now, then link that back to the original table to pull the ID like so: SELECT ID FROM MyTable AS T INNER JOIN ( SELECT Person, MAX(MeetingDate) AS MeetingDate FROM MyTable GROUP BY Person ) AS SUB ON T.Person = SUB.Person AND T.MeetingDate = SUB.MeetingDate Or using a CTE like so: WITH ...


1

In SQL Server, merge the two tables into one "from" block via a Union All. Then group by all of the columns, so you can count how many rows match on all columns. Then exclude all rows with a count of 2 (the rows match between both tables). Setup the tables based on your image captures: create table database_1 ( Serial_number int not null ...


Only top voted, non community-wiki answers of a minimum length are eligible