query failing - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query failing

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-08 : 04:41:57

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' )
where rno = 1

error:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'where'.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-08 : 04:44:21

Hi, U missed the alias name for derived table :

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-08 : 04:44:46

give an alias to the derived table...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-08 : 04:45:24

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-08 : 04:51:57

hi same query not working in sql server 2000.how can i overcome this

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-08 : 04:55:29

quote:
Originally posted by rajasekhar857

hi same query not working in sql server 2000.how can i overcome this

Refer this

http://senthilnagore.blogspot.com/2009/07/create-row-number-or-serial-no.html

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-08 : 04:57:44

can you give me query for this please

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-08 : 05:52:21

quote:
Originally posted by rajasekhar857

can you give me query for this please

Try like this

SELECT top 1 MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A' ORDER BY MED_MEDID_DESC ,
med_routed_med_id_desc

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-08 : 06:00:34

senthil nagore
so you are not using your own blogspot-solution?

The given query returns only one row.
The original query can give more rows but without "duplicates".

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-08 : 06:16:58

quote:
Originally posted by webfred

senthil nagore
so you are not using your own blogspot-solution?

The given query returns only one row.
The original query can give more rows but without "duplicates".

No, you're never too old to Yak'n'Roll if you're too young to die.

Order by multiple column is not possible in that query!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-08 : 07:41:39

I'm really not sure, but what about:


create table #EMRMedicationsTPLkup(
MED_NAME varchar(255),
ETC varchar(255),
generic_drug_name_override varchar(255),
MED_MEDID_DESC int,
med_routed_med_id_desc int,
STATUS char(1)
)
insert #EMRMedicationsTPLkup
select 'Aspirin', 'etc 1', 'generic 1',11,7,'A' union all
select 'Aspirin', 'etc 2', 'generic 5',41,20,'A' union all
select 'Aspirin', 'etc 3', 'generic 7',101,33,'A'

-- old select using row_number()
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

-- new select without row_number()
select * from
(
SELECT 
MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override,
(select count(*) from #EMRMedicationsTPLkup t2
 WHERE t2.MED_NAME <= t.MED_NAME 
   AND t2.MED_MEDID_DESC <= t.MED_MEDID_DESC
   AND t2.ETC <= t.ETC
   AND t2.generic_drug_name_override <= t.generic_drug_name_override
   AND t2.med_routed_med_id_desc <= t.med_routed_med_id_desc
   AND t2.MED_NAME = 'Aspirin') as rno
from #EMRMedicationsTPLkup t)dt
where rno = 1

drop table #EMRMedicationsTPLkup

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources