No double data in table - how to obtain this?

Other SQL Server Topics
1

hi,
how can to extract only no double row from this table ?
If the code is double, take the one with the most recent date

------------|--------|---------|---------------|
cod_cliente	|Elemento|	Prezzo |	Data       |
------------|--------|---------|---------------|
3			|CD00001 |	15,75  |	2021-01-01 |
1			|CD00001 |	15,45  |	2022-01-01 |
1			|CD00001 |	20,6   |	2018-11-01 |
4			|CD00001 |	31,84  |	2022-08-09 |
------------|--------|---------|---------------|

I would like to get this

------------|--------|---------|---------------|
cod_cliente	|Elemento|	Prezzo |	Data       |
------------|--------|---------|---------------|
3			|CD00001 |	15,75  |	2021-01-01 |
1			|CD00001 |	15,45  |	2022-01-01 |
4			|CD00001 |	31,84  |	2022-08-09 |
------------|--------|---------|---------------|

Thank you for your feedback.
Bye
Riccardo

2 

;WITH cte_no_dups AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY cod_cliente ORDER BY Data DESC) AS row_num
    FROM your_table_name
)
SELECT cod_cliente, Elemento, Prezzo, Data
FROM cte_no_dups
WHERE row_num = 1

--Edit: Added DESC to ORDER BY, originally accidentally left it off

3

Need to use:

ROW_NUMBER() OVER(PARTITION BY cod_cliente ORDER BY Data DESC)

To get the most recent row.

4

hi

hope this helps

May help performance wise

.. please see the execution plans for my solution and Scott's solution

create data script

DROP TABLE IF EXISTS #CLIENT
CREATE TABLE #CLIENT (cod_cliente INT ,Elemento VARCHAR(20), Prezzo int, Data DATE )
INSERT INTO #CLIENT SELECT 3,'CD00001',1575 ,'2021-01-01'
INSERT INTO #CLIENT SELECT 1,'CD00001',1545 ,'2022-01-01'
INSERT INTO #CLIENT SELECT 1,'CD00001',206 ,'2018-11-01'
INSERT INTO #CLIENT SELECT 4,'CD00001',3184 ,'2022-08-09'

SELECT * FROM #CLIENT

SELECT 
    MAX(cod_cliente)	 
	, MAX(Elemento) 
	, MAX(Prezzo) 
	, MAX(Data)   
FROM 
   #CLIENT 
GROUP BY 
     cod_cliente

image

image
image1683×384 22.8 KB

5

@harishgg1 The results you have for MAX(Prezzo) for cod_cliente=1 are incorrect, it should be 20,6, which would be incorrect for the date associated with the most recent row.

6

thanks Robert

you are right
my code wont work ...

:face_with_open_eyes_and_hand_over_mouth: :face_with_open_eyes_and_hand_over_mouth:

7

Thank you for your answers.
But i needed to write statement sql inside to php page and then is it possibile write normal sql statement string that not use a script into database ?
Do you think it is possible to make a select with a subselect always for the same table?
The subselect removes the doubles and the first select extracts the other fields.

Thank you for your suggestions.

8

You can use a derived table rather than a cte:


SELECT cod_cliente, Elemento, Prezzo, Data
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY cod_cliente ORDER BY Data DESC) AS row_num
    FROM your_table_name
) AS derived
WHERE row_num = 1