MSSQL · take a record according to the maximum value of multiple fields and grouping according to a field

Time:2021-7-29
Reading time |1.14 minutes word count |1825.6 characters
primary coverage |1. Introduction & background
“MSSQL · take a record according to the maximum value of multiple fields and grouping according to a field”
Author | SCscHero Writing time | 2021/7/12 PM12:0
Article type |Series Degree of completion |Completed
motto Every great cause has a trivial beginning.

1、 Introduction & background    Completion: 100%


a) Dealing with problems

Overview: query according to a field, and the records that meet the maximum values of several fields in the records.
[example] the actual problem encountered by bloggers is that there are many historical data in a table. The judgment method for extracting effective data from these historical data is: there are multiple data in each field a, only one effective data, and the other fields in the record, such as B, C and D, must be the maximum value in the same record of all field a.

b) Application scenario

For example, in a table, there are many duplicate records with the name of “AAA”, which are valid only when the other two fields such as ID and acount are the largest.

c) Effect display

MSSQL · take a record according to the maximum value of multiple fields and grouping according to a field

2、 Example & data preparation    Completion: 100%


a) Example description

At present, there is a table containing multiple records with name “a” and “B”. Among these records, there are different fields such as createtime and secid. At present, the record with createtime as the maximum and ID as the maximum should be taken.

b) Data preparation

IF EXISTS (SELECT 1
               FROM dbo.sysobjects
               WHERE id=OBJECT_ ID(N'[dbo].[Demo_ 053_ Take the maximum ID] ') and objectproperty (ID, n'isusertable') = 1) according to some two fields
				BEGIN
        DROP TABLE Demo_ 053_ Take the maximum ID according to some two fields;
    END;
    CREATE TABLE Demo_ 053_ Take the maximum ID ([ID] [bigint] not null identity (1,1) according to some two fields,
    name VARCHAR(10),
    SecID INT,
    Memo VARCHAR(20),
    CreateTime DATETIME) ON [PRIMARY];

    INSERT INTO Demo_ 053_ Take the maximum ID values ('a ', 2,' A2 (the second value of a) ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('a ', 1,' A1 -- the first value of a ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('a ', 3,' the third value of A3: a ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('b ', 1,' B1 -- the first value of B ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('b ', 3,' the third value of B3: B ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('b ', 2,' B2B2 ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('b ', 4,' b4b4 ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('b ', 5,' B5 ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('a ', 92,' b4b4 ', getdate()) according to some two fields;
    INSERT INTO Demo_ 053_ Take the maximum ID values ('b ', 7,' b5b5b5b5b5 ', getdate()) according to some two fields;

3、 Wrong attempt    Completion: 100%


a) Wrong attempt 1

SELECT a.*
FROM Demo_ 053_ Take the maximum ID a according to some two fields
WHERE 1=1 AND a.ID=(SELECT MAX(ID)
                    FROM Demo_ 053_ Take the maximum ID according to some two fields
                    WHERE name=a.name AND a.SecID=SecID)AND a.ID=(SELECT MAX(ID)
                                                                  FROM dbo.Demo_ 053_ Take the maximum ID according to some two fields
                                                                  WHERE name=a.name AND CreateTime=a.CreateTime)
ORDER BY a.name;

b) Wrong attempt 2

SELECT a.*
FROM Demo_ 053_ Take the maximum ID a according to some two fields
WHERE 1=1 AND a.SecID=(SELECT MAX(SecID)
                       FROM Demo_ 053_ Take the maximum ID according to some two fields
                       WHERE CreateTime=a.name AND a.SecID=SecID)AND a.ID=(SELECT MAX(CreateTime)
                                                                           FROM dbo.Demo_ 053_ Take the maximum ID according to some two fields
                                                                           WHERE name=a.name AND CreateTime=a.CreateTime)
ORDER BY a.name;

c) Wrong attempt 3

SELECT a.name, a.SecID, a.Memo, a.CreateTime
FROM Demo_ 053_ Take the maximum ID a according to some two fields
WHERE 1=1 AND a.ID=(SELECT MAX(ID)FROM Demo_ 053_ Take the maximum ID b where (a.secid = b.secid) and a.id = (select max (ID) according to some two fields
                                                                                               FROM dbo.Demo_ 053_ Take the maximum ID C according to some two fields
                                                                                               WHERE c.CreateTime=a.CreateTime);

4、 Solution    Completion: 100%


The latest implementation using the following T-SQL is for reference only. I also hope that DBA leaders can have better solutions to communicate together.

select a.* from Demo_ 053_ Take the maximum ID a where according to some two fields 
a.SecID = (select max(SecID) from Demo_ 053_ Take the maximum ID according to two fields (where name = a.name) 
AND A.CreateTime = (select max(CreateTime) from Demo_ 053_ Take the maximum ID (where name = a.name) order by a.name according to some two fields

5、 Statements and references    Completion: 100%


Original blog post, please do not reprint without permission.

If you are helpful, you are welcome to like, collect and pay attention. If you have any questions, please comment! If you need to contact the blogger, you can write scschero directly.