Find MAX value from another table

Question

Post reply

Find MAX value from another table

SQL Server

Old Hand

Points: 377

November 19, 2020 at 4:21 pm

Go to Answer

#3811625

Need Query help to find MAX value from another table.

CREATE TABLE #tblTest1
(InsDtTime datetime,
 RunDate date,
 AID int,
 AValue float)

 INSERT INTO #tblTest1 values ('2020-11-19 06:18:56.170','2020-11-06',21,26508071.9537433)
 INSERT INTO #tblTest1 values ('2020-11-19 06:18:56.170','2020-11-17',21,26583407.4853299)

 CREATE TABLE #tblTest2
 (InsDtTime datetime,
  RunDate date,
  AID int,
  AValue float)

insert into #tblTest2 values ('2020-11-16 06:03:02.227','2020-11-06',21,26493245.2468744)
insert into #tblTest2 values ('2020-11-11 06:14:23.757','2020-11-06',21,26493232.0619925)
insert into #tblTest2 values ('2020-11-10 06:14:51.007','2020-11-06',21,26523361.1219925)

insert into #tblTest2 values ('2020-11-18 06:17:35.047','2020-11-17',21,26568348.0453299)
insert into #tblTest2 values ('2020-11-17 06:12:23.757','2020-11-17',21,26523358.5719925)

SELECT B.InsDtTime, A.RunDate, A.AID, A.AValue AS NewValue, B.AValue AS OldValue
FROM #tblTest1 A
JOIN #tblTest2 B
ON A.RunDate = B.RunDate
AND A.AID = B.AID

The above query works fine, but What I want is to get the latest (MAX) value from #tblTest2 to be displayed as OldValue.

Expected Output:

ExpectedOutput

Steve Collins

SSCrazy

Points: 2174

November 19, 2020 at 4:30 pm

Answer

#3811627

with t2_latest_cte as (
    select *, row_number() over (partition by RunDate 
                                 order by InsDtTime desc) rn
    from #tblTest2)
select b.InsDtTime, a.RunDate, a.AID, 
       A.AValue as NewValue, b.AValue as OldValue
from #tblTest1 a
     join t2_latest_cte b on a.RunDate=b.RunDate
                             and a.AID=b.AID
                             and b.rn=1;

InsDtTimeRunDateAIDNewValueOldValue
2020-11-16 06:03:02.2272020-11-062126508071.953743326493245.2468744
2020-11-18 06:17:35.0472020-11-172126583407.485329926568348.0453299

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101124 More actions · Answer 1


SELECT B.InsDtTime, A.RunDate, A.AID, A.AValue AS NewValue, B.AValue AS OldValue
FROM #tblTest1 A
CROSS APPLY (
    SELECT TOP (1) *
    FROM #tblTest2 B
    WHERE B.RunDate = A.RunDate
    AND A.AID = B.AID
    ORDER BY InsDtTime DESC
) AS B

This reply was modified 4 years, 1 month ago by ScottPletcher. Reason: Corrected bugs in the query

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

Just some remarks about the way you write SQL. The use of the affix "tbl" is a design flaw called a tibble. It violates the rule about mixing data and metadata in the schema. Why don't you seem to know that a table by definition, must have a key? I'm sure that your "a_value" really needs to be float. . However, an identifier is never an integer. Because it's on a nominal scale. Here's my guess as to what you you meant. I hope you do have access to the current versions of SQL server, so you don't have to use these old datatypes.

CREATE TABLE Foobars

(ins_timestamp DATETIME2(0) NOT NULL,

run_date DATE NOT NULL,

alpha_id CHAR(3) NOT NULL,

alpha_value FLOAT NOT NULL,

foobar_type CHAR(7) NOT NULL

CHECK(foobar_type IN ('current', 'prior'))

PRIMARY KEY (????)); --- please fix this. I do not wish to make guesses.

You might want to start using the ANSI/ISO standard table constructor syntax instead of the old Sybase SQL Server row by row syntax:

INSERT INTO Foobars

VALUES

('2020-11-19 06:18:56', '2020-11-06', 21, 26508071.9537433, 'current'),

('2020-11-19 06:18:56', '2020-11-17', 21, 26583407.4853299, 'current');

Your second table has exactly the same structure as your first table. This does not work in an RDBMS like it did when you are using punch cards. Google around for some of the remarks that Chris Date and David McGovern have written about this horrible flaw. I refer to it as attribute splitting. For example, if you had a table for Female_Personnel and a table for Male_Personnel, you would have split a table for Personnel on the attribute "sex code" to generate two improperly designed tables.

INSERT INTO Foobars

VALUES

('2020-11-16 06:03:02', '2020-11-06', 21, 26493245.2468744, 'prior'),

('2020-11-11 06:14:23', '2020-11-06', 21, 26493232.0619925, 'prior'),

('2020-11-10 06:14:51', '2020-11-06', 21, 26523361.1219925, 'prior'),

('2020-11-18 06:17:35', '2020-11-17', 21, 26568348.0453299, 'prior'),

('2020-11-17 06:12:23', '2020-11-17', 21, 26523358.5719925, 'prior');

This design still looks awful. I'm going to make a guess that the current foobar is the row with the most recent timestamp, and that the prior value is the second most recent foobar. Is this correct? If so, get rid of the foobar_type column and use the LAG() function.

Please post DDL and follow ANSI/ISO standards when asking for help.