Home » SQL & PL/SQL » SQL & PL/SQL » SQL query - need help
SQL query - need help [message #202070] Wed, 08 November 2006 01:42 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi guys,

I need help for the following query.

I don't want to show PATIENTNUMBER where
RID is only one.

it should show one and above RID.


select distinct statusid,tocompany,tofaxnumber,patientnumber,rid
from (select b.statusid,b.tocompany,b.tofaxnumber,a.patientnumber,
( row_number () over (partition by a.patientnumber order by a.patientnumber)) as rid
from tblfax a, tblfaxdestination b
where a.FAXID = b.faxid and A.createddatetime > '05-NOV-2006')

STATUSID	TOCOMPANY	                TOFAXNUMBER	PATIENTNUMBER	RID

40	CRITICAL CARE SYSTEMS, INC.	        480-897-2957	416		1
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	6056		1
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	6056		2
40	AMERICAN PREMIER HOME HEALTHCARE, LLC	602-287-0005	15528		1
40	LOVELACE PRECERT-COMM,SR,LINC	        505-232-1707	22953		1
40	HERITAGE HOME HEALTHCARE, INC.	        505-366-2325	22953		2
40	HERITAGE	                        505-232-3337	22953		3
40	HERITAGE HOME HEALTHCARE, INC.  	505-366-2325	22953		4
40	KCI USA, INC				888-245-2295	31243		1
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	45501		1
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	45501		2



Desired result


STATUSID	TOCOMPANY	                TOFAXNUMBER	PATIENTNUMBER	RID


40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	6056		1
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	6056		2
40	LOVELACE PRECERT-COMM,SR,LINC	        505-232-1707	22953		1
40	HERITAGE HOME HEALTHCARE, INC.	        505-366-2325	22953		2
40	HERITAGE	                        505-232-3337	22953		3
40	HERITAGE HOME HEALTHCARE, INC.  	505-366-2325	22953		4
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	45501		1
40	SUNRISE SENIOR LIVING MANAGEMENT INC	520-290-1900	45501		2

Re: SQL query - need help [message #202072 is a reply to message #202070] Wed, 08 November 2006 01:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
use where exists...
or group by having if RID is unique per patient
Re: SQL query - need help [message #202079 is a reply to message #202072] Wed, 08 November 2006 02:38 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks a lot , Frank , for your reply.

I have tried following but that is giving me
the same result.

Could you help me to figured out the query?


select distinct statusid,tocompany,tofaxnumber,patientnumber,rid
from (select b.statusid,b.tocompany,b.tofaxnumber,a.patientnumber,
( row_number () over (partition by a.patientnumber order by a.patientnumber)) as rid
from tblfax a, tblfaxdestination b
where a.FAXID = b.faxid and A.createddatetime > '05-NOV-2006'
 )
 where  exists (select unique rid from ( select ( row_number () over (partition by a.patientnumber order by a.patientnumber)) as rid
from tblfax a, tblfaxdestination b
where a.FAXID = b.faxid and A.createddatetime > '05-NOV-2006'))


Re: SQL query - need help [message #202087 is a reply to message #202079] Wed, 08 November 2006 03:01 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
use the having clause..you could create a view to make life simpler
Previous Topic: Snapshots & Materialized views
Next Topic: Adding column
Goto Forum:
  


Current Time: Tue Dec 06 04:07:54 CST 2016

Total time taken to generate the page: 0.14508 seconds