Solved

SQL Question - Retrieve latest records per initial

Posted on 2016-08-31
8
74 Views
Last Modified: 2016-09-01
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.
0
Comment
Question by:bfuchs
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41779233
Something like:
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
) 

Open in new window


should work okay.

Good luck!
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41779988
you can try like this.
;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

Open in new window


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
*/

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 41780515
Hi Experts,

Just arrived at work, will test them and let you know.

Thanks,
Ben
0
 
LVL 3

Author Comment

by:bfuchs
ID: 41780616
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:Sharath
ID: 41780653
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

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 41780720
@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
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 41780736
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

Open in new window

0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 41780788
Excelent!!

Thank You!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now