September 8, 2009 at 3:04 am
i need the following query in 2000.please look into this.
Select * from (
SELECT MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override,Row_number() OVER (PARTITION BY MED_NAME
ORDER BY MED_MEDID_DESC ,
med_routed_med_id_desc ) rno
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A' ) AS t
where rno = 1
September 8, 2009 at 5:07 am
rajasekhar857 (9/8/2009)
i need the following query in 2000.please look into this.Select * from (
SELECT MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override,Row_number() OVER (PARTITION BY MED_NAME
ORDER BY MED_MEDID_DESC ,
med_routed_med_id_desc ) rno
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A' ) AS t
where rno = 1
how does this work for you?
if object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp
create table #temp (RowID int identity(1,1) PRIMARY KEY CLUSTERED,
MED_NAME varchar(50),
MED_MEDID_DESC varchar(50),
MED_ROUTED_MED_ID_DESC varchar(50),
ETC,
generic_drug_name_override
)
insert into #temp (MED_NAME, MED_MEDID_DESC, MED_ROUTED_MED_ID_DESC, ETC, generic_drug_name_override)
select MED_NAME, MED_MEDID_DESC, MED_ROUTED_MED_ID_DESC, ETC, generic_drug_name_override
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A'
select MED_NAME, MED_MEDID_DESC, MED_ROUTED_MED_ID_DESC, ETC, generic_drug_name_override
from #temp t1
INNER JOIN (select MED_NAME, MED_MEDID_DESC, MED_ROUTED_MED_ID_DESC, ETC, MIN(RowID)
from #temp
group by select MED_NAME, MED_MEDID_DESC, MED_ROUTED_MED_ID_DESC) t2 ON t1.RowID = t2.RowID
if object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature. If you had provided this, I wouldn't have had to guess at many things in this post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply