max date order by status
-
Hi Expert,
i wanted to fetch status and max new date , value data
create table test
(newdate date,status varchar(10, value varchar(10))
insert into test
values('01-07-2020','Newone','segmentone'),('07-07-2020','Newone','segmenwo'),('09-07-2020','Newtwo','segmenthee'),('10-07-2020','Newtwo','segmenthee')
expected output attached
-
Your code does not run properly. The create table statement throws an error.
Can you use the CODE button to properly insert code?
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/ -
Does this get what you need? I would avoid using reserved words as column names (status and value).
Some people prefer to use a CTE, but I like derived tables.
SELECT x.newdate, x.[status], x.[value]
FROM (
SELECT newdate, [status], [value],
ROW_NUMBER() OVER (PARTITION BY [status] ORDER BY newdate DESC) AS RowNum
FROM dbo.test
) AS x
WHERE x.RowNum = 1;
WITH this AS
(
SELECT newdate, [status], [value],
ROW_NUMBER() OVER (PARTITION BY [status] ORDER BY newdate DESC) AS RowNum
FROM dbo.test
)
SELECT newdate, [status], [value]
FROM this
WHERE RowNum = 1; -
From the data provided the latest newdate for Status = 'Newtwo' should be 10-07-2020, not 10-09-2020, as per required result.
Assuming that's right, this should do:
select Max(NewDate) LatestNewDate, Status
from test
group by status
order by LatestNewDate_____________
Code for TallyGenerator -
HI Expert,
This is perfect. but the data needs max(date) should be order by same day and not all calendar days
insert into test
values
('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmenwo'),
('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')
expected output:
('2022-01-04 19:00:57','Newone','segmenwo'),
('2022-04-04 19:00:57','Newtwo','segmenfour')
- Shree23 wrote:
HI Expert,
insert into test values
('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmenwo'), ('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')
Your table has a DATE data type. Try changing it to DATETIME.
-
Hi Expert,
Hi Expert,
there is condition when value is same for the status then it should take min date and if the value is change for the status then max date
create table test
(newdate datetime,status varchar(10), value varchar(10))
insert into test
values
('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmentone'),
('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')
expected output
('2022-01-04 18:00:57','Newone','segmentone')
('2022-04-04 19:00:57','Newtwo','segmentfour')
-
ShambhuRai-4099 JingyangLi · 3 minutes ago
when value is same for the status then it should take min date
expected output
('2022-01-04 18:00:57','Newone','segmentone')
when the value is change for the status then max date
expected output
('2022-04-04 19:00:57','Newtwo','segmentfour')
-
suggestion please
-
suggestion pls
- Shree23 wrote:
suggestion pls
How about showing us what you have actually tried? Have you even attempted to write this query yourself???
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/ -
i tried
;with mycte as (
select newdate,status,value
,count(*) over(Partition by status,value) cnt
,row_number() Over(Partition by status Order by newdate) rnAsc
,row_number() Over(Partition by status Order by newdate DESC) rnDesc
from test
)
select newdate,status,value
from mycte
WHERE (cnt>1 and rnAsc=1) or (cnt=1 and rnDesc=1)
and its working but i am getting same values for the status frequently and i should consider min newdate?how to do it
example
INSERT INTO TEST
VALUES
('2022-03-31 12:52:49','Newtwo', '6-1'),
('2022-04-01 12:52:49','Newtwo', '6-1'),
('2022-04-28 15:58:40','Newtwo', '6-1'),
('2022-04-29 12:52:49','Newtwo', '6-1'),
('2022-04-30 12:52:49','Newtwo', '6-1'),
('2022-05-01 12:52:49','Newtwo', '6-1'),
('2022-05-29 12:52:49','Newtwo', '6-1'),
('2022-05-31 12:52:49','Newtwo', '6-1'),
('2022-06-01 12:52:49','Newtwo', '6-1'),
('2022-06-02 12:52:49','Newtwo', '6-1')
in the above case it
expected output
('2022-03-31 12:52:49','Newtwo', '6-1')
-
This was removed by the editor as SPAM
-
suggestion please!!
-
This is not an answer factory. Crack open a textbook if need be and actually learn something for yourself. If you want answers without doing any work, open your wallet.
No skin off my nose if you don't get any answers. If you want help, read Jeff's article on how to ask a good question, and follow the instructions in it. People here are generally more than happy to help you if you make it easy for them. That's what Jeff's article is all about. Do everyone a favor and read it and follow the instructions.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply