Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
SQL Question - Retrieve latest records per initial
Hi Experts,
I have a table named HistoryChanges with following fields
ID (PK) Int Auto num
Initial varchar (5)
DateEntered DateTime
Other columns are not relevant
Would like to get all records from a given initial which were entered latest.
Meaning if Initial A entered first 5 records then Initial B entered another 5, later Initial A entered 2 records,
If I'm looking for Initial A I would only want see the last two records, while if searching for Initial B I would want see 5 records.
This could be determined either by ID which is auto num or by DateEntered which captures date and time record was entered.
PS, I have SQL Express 2005.
I have a table named HistoryChanges with following fields
ID (PK) Int Auto num
Initial varchar (5)
DateEntered DateTime
Other columns are not relevant
Would like to get all records from a given initial which were entered latest.
Meaning if Initial A entered first 5 records then Initial B entered another 5, later Initial A entered 2 records,
If I'm looking for Initial A I would only want see the last two records, while if searching for Initial B I would want see 5 records.
This could be determined either by ID which is auto num or by DateEntered which captures date and time record was entered.
PS, I have SQL Express 2005.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Something like:
should work okay.
Good luck!
SELECT * FROM [dbo].[MyTable]
WHERE DateEntered > (
-- The date of second most recent initial.
SELECT TOP 1 DateEntered
FROM [dbo].[MyTable] a
where Initial NOT IN (
-- The most recent initial
SELECT TOP 1 Initial
FROM [dbo].[MyTable] a
ORDER BY DateEntered DESC
)
ORDER BY DateEntered DESC
)
should work okay.
Good luck!
you can try like this.
Tested with this sample data.
;with cte as (
select ID - row_number() over (partition by initial order by ID) rn ,*
from HistoryChanges),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
order by id
Tested with this sample data.
declare @HistoryChanges table (ID int identity, initial varchar(5), DateEntered datetime)
insert @HistoryChanges values ('A', '2016-08-30 10:00:00')
insert @HistoryChanges values ('A', '2016-08-30 10:01:00')
insert @HistoryChanges values ('A', '2016-08-30 10:02:00')
insert @HistoryChanges values ('B', '2016-08-30 10:03:00')
insert @HistoryChanges values ('B', '2016-08-30 10:04:00')
insert @HistoryChanges values ('A', '2016-08-30 10:05:00')
insert @HistoryChanges values ('A', '2016-08-30 10:06:00')
;with cte as (
select ID - row_number() over (partition by initial order by ID) rn ,*
from @HistoryChanges),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
order by id
/*
ID initial DateEntered
4 B 2016-08-30 10:03:00.000
5 B 2016-08-30 10:04:00.000
6 A 2016-08-30 10:05:00.000
7 A 2016-08-30 10:06:00.000
*/
Hi,
I guess was not clear enough.
I need to get results from a particular Initial only, and see which were this initial latest entries,
Possible your example does that, however I'm not sure where do I specify which initial i'm looking for?
Thanks,
Ben
I guess was not clear enough.
I need to get results from a particular Initial only, and see which were this initial latest entries,
Possible your example does that, however I'm not sure where do I specify which initial i'm looking for?
Thanks,
Ben
try like this.
declare @initial varchar(5)
select @initial = 'A'
;with cte as (
select ID - row_number() over (partition by initial order by ID) rn ,*
from HistoryChanges
where initial = @initial),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
order by id
@Sarath,
This looks like working:)
Just one question, where do I enter additional criteria besides initial?
for example where tablename = 'somevalue' and initial is not null, meaning if initial is null then it should not count as different value and therefore consider as cut off point.
In other words if we have initial AA record 1,2,3 and null record 4 and AA record 5, I do want see 123 and 5.
Thanks,
Ben
This looks like working:)
Just one question, where do I enter additional criteria besides initial?
for example where tablename = 'somevalue' and initial is not null, meaning if initial is null then it should not count as different value and therefore consider as cut off point.
In other words if we have initial AA record 1,2,3 and null record 4 and AA record 5, I do want see 123 and 5.
Thanks,
Ben
Well. In that case, you cannot rely on your auto increment ID. You can try like this.
declare @initial varchar(5)
select @initial = 'A'
;with cte as (
select row_number() over (order by ID) - row_number() over (partition by initial order by ID) rn ,*
from HistoryChanges
where initial is not null),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
and initial = @initial
order by id
Premium Content
You need an Expert Office subscription to comment.Start Free Trial