April 20, 2022 at 4:08 pm
Hi Expert,
I wanted to fetch min date records where value column data is repeated
create table test3
(newdate datetime,status varchar(22),value varchar(22)
)
insert into test3
values
('2021-06-16 09:44:47.000', 'Newthree', '6 - 1'),
('2022-04-01 18:00:57.000', 'Newthree', '6 - 1'),
('2022-03-28 15:58:40.000', 'Newtwo', '3 - Test'),
('2022-03-31 15:20:17.000', 'Newtwo', '8 - MO'),
('2022-04-01 18:20:27.000', 'Newtwo', '8 - MO'),
('2022-03-28 12:48:46.000', 'Newone', '4 - Dead'),
('2022-04-01 18:20:28.000', 'Newone', '7 - Lost')
expected output
('2021-06-16 09:44:47.000', 'Newthree', '6 - 1')
('2022-03-28 15:58:40.000', 'Newtwo', '3 - Test')
('2022-03-31 15:20:17.000', 'Newtwo', '8 - MO'),
('2022-03-28 12:48:46.000', 'Newone', '4 - Dead'),
('2022-04-01 18:20:28.000', 'Newone', '7 - Lost')
April 20, 2022 at 4:56 pm
Can you do us a favor? If you are going to expect us to write your code for you, can you at least follow the guidelines that make it far easier to provide an answer?
Here is the link.
http://qa.sqlservercentral.com/articles/61537/
Please read the article.
And, have you tried the MIN function and a GROUP BY????
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 6, 2022 at 5:55 am
sure
May 6, 2022 at 7:49 am
The following query will satisfy your requirements
WITH cteData AS (
SELECT t.*
, rn = ROW_NUMBER() OVER ( PARTITION BY t.status, t.value
ORDER BY t.newdate )
FROM test3 AS t
)
SELECT d.status, d.value, d.newdate
FROM cteData AS d
WHERE d.rn = 1;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply