The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Course Of The Month
9 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Question - Retrieve latest records per initial
Posted on 2016-08-31
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
8 Comments
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!
Active today
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
*/
Active 1 day ago
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
Active today
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
Active 1 day ago
@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
Active today
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
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud.
This article shows how to create one of these functions to write directly to Azure Table Storage.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 19 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.