More field conditions, eliminate duplicate data only take the latest data-CodePudding

Consult everybody, I want to filter a batch of data, according to the ID departID maximum of a set of data and other data to remove (as shown in figure only remove the yellow background data),
Really have a clue, consult experts,

CodePudding user response:

According to the name is the biggest data detected departid

CodePudding user response:

1/f,

reference 姎 jacaranda response:

according to the name is the biggest data detected departid?

According to the ID departID maximum of a set of data

CodePudding user response:

 
Select ID, Max (departID) departID, name the from (
Select 1 as ID, 101 departID, 'apple' name union all 
As ID, select 1 101 departID 'orange' name union all 
As ID, select 1 101 departID 'grapes' name union all 
Select 1 as ID, 102 departID, 'apple' name union all 
As ID, select 1 102 departID 'orange' name union all 
As ID, select 1 102 departID 'grapes' name union all 
Select 2 as ID, 151 departID, 'apple' name union all 
Select 2 as ID, 151 departID 'orange' name union all 
Select 2 as ID, 151 departID 'grapes' name union all 
Select 2 as ID, 115 departID, 'apple' name union all 
Select 2 as ID, 115 departID 'orange' name union all 
Select 2 as ID, 115 departID 'grapes' name union all 
Select 2 as ID, 113 departID, 'apple' name union all 
Select 2 as ID, 113 departID 'orange' name union all 
Select 2 as ID, 113 departID 'grapes' name) a group by ID, name the order by ID

CodePudding user response:

Select a. *, b.n ame
The from
(select ID, Max (departID) departID
The from
(select 1 as ID, 101 departID, 'apple' name union all
As ID, select 1 101 departID 'orange' name union all
As ID, select 1 101 departID 'grapes' name union all
Select 1 as ID, 102 departID, 'apple' name union all
As ID, select 1 102 departID 'orange' name union all
As ID, select 1 102 departID 'grapes' name union all
Select 2 as ID, 151 departID, 'apple' name union all
Select 2 as ID, 151 departID 'orange' name union all
Select 2 as ID, 151 departID 'grapes' name union all
Select 2 as ID, 115 departID, 'apple' name union all
Select 2 as ID, 115 departID 'orange' name union all
Select 2 as ID, 115 departID 'grapes' name union all
Select 2 as ID, 113 departID 'orange' name union all
Select 2 as ID, 113 departID 'sugar' name union all
Select 2 as ID, 113 departID 'pineapple' name
) a group by ID order by ID
) as a
Inner join
(select 1 as ID, 101 departID, 'apple' name union all
As ID, select 1 101 departID 'orange' name union all
As ID, select 1 101 departID 'grapes' name union all
Select 1 as ID, 102 departID, 'apple' name union all
As ID, select 1 102 departID 'orange' name union all
As ID, select 1 102 departID 'grapes' name union all
Select 2 as ID, 151 departID, 'apple' name union all
Select 2 as ID, 151 departID 'orange' name union all
Select 2 as ID, 151 departID 'grapes' name union all
Select 2 as ID, 115 departID, 'apple' name union all
Select 2 as ID, 115 departID 'orange' name union all
Select 2 as ID, 115 departID 'grapes' name union all
Select 2 as ID, 113 departID 'orange' name union all
Select 2 as ID, 113 departID 'sugar' name union all
Select 2 as ID, 113 departID 'pineapple' name) as b
On a. d. epartid=b.d epartid

CodePudding user response:

Can use a subquery, first take out each ID corresponding maximum departID, again inner join out name

 DECLARE @ t TABLE (ID INT the NOT NULL, departID INT the NOT NULL, [name] NVARCHAR (100) NOT NULL) 
INSERT @ t (ID, departID, name) VALUES 
(1, 101, 'apple'), (1, 101, 'orange'), (1, 101, 'grapes'), (1, 102,' apple '), 
(1, 102, 'orange'), (1, 102, 'grapes'), (2, 151,' apple '), (2, 151, 'orange'), 
(2, 151, 'grapes'), (2, 115,' apple '), (2, 115, 'orange'), (2, 115, 'grapes'), 
(2, 113, 'apple'), (2, 113, 'orange'), (2, 113, 'grapes') 

B. SELECT * FROM (
SELECT id, MAX (departID) departID FROM @ t GROUP BY id) a INNER JOIN @ t b ON b.I D=Anderson D AND a. d. epartID=b.d epartID 
The ORDER BY b.i d, b.d epartID, b. [name]

CodePudding user response:

Select id, Max (deparid), the name into table2 the from table1 group by id, name