Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 10:59:12

Hi,

I have the following select query which works fine;

Select * from dbo.therapyData_566
where issueseq = 0
order by patid, prodcode, eventdate

Results

patid eventdate consid prodcode
2 01/01/02 101 1
2 02/01/02 102 1
2 08/02/02 103 1
4 10/02/03 183 5
5 15/04/04 203 8
5 18/06/06 206 8

I want my query to do th efollowing;

a) check if the prodcode is unique for that particular patid then ignore it; therefore in this case patid = 4 record will be ignored

b) Check for each patid the prodcodes and if they are more than one prodcode then ignore the one with the earliest eventdate.. in the above example:

patid eventdate consid prodcode
2 01/01/02 101 1
5 15/04/04 203 8

Therefore my updated query should produce the results:

patid eventdate consid prodcode
2 02/01/02 102 1
2 08/02/02 103 1
5 18/06/06 206 8

Any help please!!!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-19 : 11:07:07

row_number() comes to mind.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 11:10:41

any help of how to incorporate it .. Thank you

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-19 : 11:13:28

partition by patid, only select rows with a rownumber > 1.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 11:20:27

so how will I update the query ?

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 11:31:51

Any help to update the code below;

with cte as (
	select 
	 patid, 
	 prodcode, 
	 eventdate, 
	 consid,
	  RowNum = ROW_NUMBER() OVER (ORDER BY patid, prodcode, eventdate) 
	from dbo.therapyData_566
where issueseq = 0
	)
select b.*
from cte a
	inner join cte b
		on a.RowNum > 1

Many thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-19 : 11:39:14

whats wrong with


SELECT * FROM 
(
	select 
	 patid, 
	 prodcode, 
	 eventdate, 
	 consid,
	  RowNum = ROW_NUMBER() OVER (ORDER BY patid, prodcode, eventdate) 
	from dbo.therapyData_566
where issueseq = 0
	) a
WHERE RowNum > 1

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 11:44:50

it doesnt give me the results I requested from # 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 11:48:04

Not quite clear to me the relationship between patid and prodcode - in your example, it seems like for a given patid, the prodcode is always the same. In any case, you will need a partition by something. Based on my limited understanding, it may be something like this:

with cte as (
	select 
	 patid, 
	 prodcode, 
	 eventdate, 
	 consid,
	  RowNum = ROW_NUMBER() OVER (partition by patid  ORDER BY patid, prodcode, eventdate eventdate) 
	from dbo.therapyData_566
where issueseq = 0
	)
select b.*
from cte a
	inner join cte b
		on a.RowNum > 1
SELECT * FROM cte WHERE RowNum > 1

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 12:03:19

The relationship between patid to prodcode is many is to many.. Sorry my example might not have been that clear.. but for a given patid the prodcode might be different..

Let me provide another example and what I expect;

patid eventdate consid prodcode RowNum

8566 1995-06-12 54 1 2
8566 1995-06-12 54 1 3
8566 1995-06-12 54 1 4
8566 1995-06-12 54 1 5
8566 1995-06-12 54 1 6
8566 1995-06-12 54 1 7
8566 1995-06-12 54 1 8
8566 1995-07-05 55 1 9
8566 1995-07-05 55 1 10
8566 1995-08-02 47 1 11
8566 1995-08-02 47 1 12
8566 1995-08-02 47 1 13
8566 1995-08-02 47 1 14
8566 1995-08-30 51 1 15
51566 2005-09-23 2216 1083 2
51566 2006-02-16 2227 1309 2
51566 2006-02-16 2227 3699 2

I expect the results to be;

patid eventdate consid prodcode RowNum

~~8566 1995-06-12 54 1 2~~
8566 1995-06-12 54 1 3
8566 1995-06-12 54 1 4
8566 1995-06-12 54 1 5
8566 1995-06-12 54 1 6
8566 1995-06-12 54 1 7
8566 1995-06-12 54 1 8
~~8566 1995-07-05 55 1 9~~
8566 1995-07-05 55 1 10
~~8566 1995-08-02 47 1 11~~
8566 1995-08-02 47 1 12
8566 1995-08-02 47 1 13
8566 1995-08-02 47 1 14
~~8566 1995-08-30 51 1 15~~
~~51566 2005-09-23 2216 1083 2~~
~~51566 2006-02-16 2227 1309 2~~
~~51566 2006-02-16 2227 3699 2~~

record 1, 2 and 3 are removed as for that patid and consid there is more than one unique prodcode and the one on the top is hence discarded..

record 4,5,6 and 7 are omitted as the consid for the patid is unique..

I am looking for repeats.... kind of

Many thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 12:29:25

Looks like you will need to partition by prodcode and patid rather than just by patid.


RowNum = ROW_NUMBER() OVER (partition by patid, prodcode  ORDER BY  eventdate)

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-19 : 12:34:26

Note changes in the previous query where more records were added were prodcode is not necessary 1 but any other number and the records are not fully removed.

Tried to do the partion as

RowNum = ROW_NUMBER() OVER (partition by patid, prodcode ORDER BY eventdate)

but never received the requied results..

The results is stil the same any help please

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 13:49:00

The following code does produce the output that you have shown. However, I was doing it just by looking at the output - I have not understood the logic, so...


CREATE TABLE #tmp (patid INT, eventdate DATETIME, consid int, prodcode INT, RowNum INT);

insert into #tmp values ('8566','1995-06-12','54','1','2');
insert into #tmp values ('8566','1995-06-12','54','1','3');
insert into #tmp values ('8566','1995-06-12','54','1','4');
insert into #tmp values ('8566','1995-06-12','54','1','5');
insert into #tmp values ('8566','1995-06-12','54','1','6');
insert into #tmp values ('8566','1995-06-12','54','1','7');
insert into #tmp values ('8566','1995-06-12','54','1','8');
insert into #tmp values ('8566','1995-07-05','55','1','9');
insert into #tmp values ('8566','1995-07-05','55','1','10');
insert into #tmp values ('8566','1995-08-02','47','1','11');
insert into #tmp values ('8566','1995-08-02','47','1','12');
insert into #tmp values ('8566','1995-08-02','47','1','13');
insert into #tmp values ('8566','1995-08-02','47','1','14');
insert into #tmp values ('8566','1995-08-30','51','1','15');
insert into #tmp values ('51566','2005-09-23','2216','1083','2');
insert into #tmp values ('51566','2006-02-16','2227','1309','2');
insert into #tmp values ('51566','2006-02-16','2227','3699','2');


with cte as 
(
   select 
      patid, 
      prodcode, 
      eventdate, 
      consid,
      RowNum,
      RN = ROW_NUMBER() OVER (partition by patid,consid,prodcode  ORDER BY eventdate) 
   from 
      #tmp
)
SELECT * FROM cte WHERE RN > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources