Solved

Count First Instance of a Value

Posted on 2014-02-18
4
634 Views
Last Modified: 2014-02-18
I have a result set that dumps data as such:

ID      DATE/TIME
AAA1077	2013-01-28 21:50:00.000
AAA1077	2013-01-28 21:50:00.000
AAA1077	2013-01-28 21:50:00.000
AAA1077	2013-01-28 21:50:00.000
AAAA055	2013-01-03 09:05:00.000
AAAA055	2013-01-03 09:05:00.000
AAAA055	2013-01-03 09:05:00.000
AAAA055	2013-01-03 09:05:00.000
AAAB405	2013-01-03 22:17:00.000
AAAB405	2013-01-03 22:17:00.000
AAAB405	2013-01-03 22:17:00.000
AAAD127	2013-01-05 23:02:00.000
AAAD127	2013-01-05 23:02:00.000
AAAD127	2013-01-05 23:02:00.000
AAAD127	2013-01-05 23:02:00.000
AAAE160	2013-01-06 20:37:00.000
AAAE160	2013-01-06 20:37:00.000
AAAE160	2013-01-06 20:37:00.000

Open in new window


How can I create a 3rd column and have it flag a 1 for this first occurrence of each Id and then 0's for and duplicates?

I know I can COUNT DISTINCT, but I need this format mentioned for the presentation layer.
0
Comment
Question by:LCNW
[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
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39868702
check this out

declare @T TABLE 
(
ID VARCHAR(30), dt DATETIME
)
INSERT INTO @T VALUES 
('AAA1077','2013-01-28 21:50:00.000')
,('AAA1077','2013-01-28 21:50:00.000')
,('AAA1077','2013-01-28 21:50:00.000')
,('AAA1077','2013-01-28 21:50:00.000')
,('AAAA055','2013-01-03 09:05:00.000')
,('AAAA055','2013-01-03 09:05:00.000')
,('AAAA055','2013-01-03 09:05:00.000')
,('AAAA055','2013-01-03 09:05:00.000')
,('AAAB405','2013-01-03 22:17:00.000')
,('AAAB405','2013-01-03 22:17:00.000')
,('AAAB405','2013-01-03 22:17:00.000')
,('AAAD127','2013-01-05 23:02:00.000')
,('AAAD127','2013-01-05 23:02:00.000')
,('AAAD127','2013-01-05 23:02:00.000')
,('AAAD127','2013-01-05 23:02:00.000')
,('AAAE160','2013-01-06 20:37:00.000')
,('AAAE160','2013-01-06 20:37:00.000')
,('AAAE160','2013-01-06 20:37:00.000')

;WITH C AS
(
SELECT *,ROW_NUMBER() OVER(partition by ID order by dt) rn FROM @T
)
SELECT id,dt,CASE RN WHEN 1 THEN 1 ELSE 0 END AS RN FROM C

Open in new window

0
 
LVL 1

Author Comment

by:LCNW
ID: 39868718
Thanks. I tweaked it to fit and it worked.

CASE WHEN ROW_NUMBER() OVER(partition by Id order by DateTime) = 1 THEN 1 ELSE 0 END AS [Flagged] 

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39868728
Cool.
0
 
LVL 1

Author Closing Comment

by:LCNW
ID: 39868730
Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

690 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