CODEWITHSUNDEEP
sqlsql-server
Black
Black

Reputation: 5367

MSSQL select only last record values by distinct column value

MS Sql Server (14.0)

I have a table that was created by a non-forward-thinking developer:

create table product_history
(
    product_code varchar(10),
    product_description varchar(100),
    price numeric(10,3)
);

He didn't create an Index, or an Identity column.

And people have been adding records to the table:

insert into product_history values ('a','apples', 3);
insert into product_history values ('b','banana', 6);
insert into product_history values ('a','apple', 6);

Now I want to select only distinct records based on when the order they were inserted (i.e. rowno ascending)

so the result set I'd like is:

'b', 'banana', 6

'a', 'apple', 6

How do I create a query that will select all the column values from only the last record by distinct product_code?

Upvotes: 1

Views: 144

Answers (2)

jmm312
jmm312

Reputation: 618

Since there's no id or timestamp value present, it's going to be hard to figure out which one was the last entry. A possible solution would be to create a copy of the table with additional columns for id and timestamp and create a trigger against the old table to update the new table whenever there's an insert. Here's the reference on how to create a trigger:

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  
  
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

You would then write your query against this new table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

SQL tables represent unordered sets. There is no such thing as a last record. The table should be created as:

create table product_history (
    product_history_id int identity(1, 1) primary key,
    product_code varchar(10),
    product_description varchar(100),
    price numeric(10,3)
);

Then you could simply do:

select ph.*
from product_history ph
where ph.product_history_id = (select max(ph2.product_history_id)
                               from product_history ph2
                               where ph2.product_code = ph.product_code
                              );

You could do essentially the same thing if there where a created_at column, such as:

created_at datetime default getdate()

There is not really much that you can do to address this. I would suggest starting over and keeping the history from today. That may seem extreme, but in a year or two, you'll have history.

If you have snapshots or backups of the database, you can go back and attempt to reconstruct the history using a correct data model. Or might might be able to use the transaction log, assuming prices were changed using updates.

If you know that prices are always increasing (or always decreasing), you could use that information to get the most recent price.

Upvotes: 3

Related Questions