Is this possible - 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

Is this possible

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-03 : 17:36:43

I have a view that can be represented by
item1 item2 value
a b dog
b a dog
c e cat

What I need is the SQL to get a view with the unique value AND with the items1,2 - other fields. a and b are interchangeable first/2nd entries. View result like this:

a b dog
c e cat

Thanks

andrewcw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-03 : 17:46:06

Are you using SQL Server 2005? If yes, then you can use ROW_NUMBER() function. If no, then you'd need to use a derived table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 17:58:48

select distinct
case when item1 <= item2 then item1 else item2 end as item1,
case when item1 <= item2 then item2 else item1 end as item2,
value
from table1

E 12°55'05.63"
N 56°04'39.26"

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-03 : 17:59:38

Its an access database with a view. Can you give me a clue on how to approach it ? Thanks

andrewcw

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 18:05:10

select distinct
iif(item1 <= item2 ; item1 ; item2 ) as item1,
iif(item1 <= item2 ; item2 ; item1 ) as item2,
value
from table1

E 12°55'05.63"
N 56°04'39.26"

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-03 : 18:10:25

I get this error -
Syntax error in query expression 'iif (item1<=item2 ; item1; item2)'

any ideas ?

andrewcw

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-04 : 00:35:13

try if instead of iif

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-04 : 01:32:57

Thanks very much for your suggestion. I tried if instead of iif and I still get the same syntax error. Did you actually try it & found within access it works ? Sometimes things in ACCESS works but does not when called by .NET like * or % but in ACCESS it does not give a syntax error. Reverse is another example of an operation that works in an ACCESS query, but not when the QUERY is called by .NET

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:42:24

try with , instead of ;


select distinct
iif(item1 <= item2 , item1 , item2 ) as item1,
iif(item1 <= item2 , item2 , item1 ) as item2,
value
from table1

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-04 : 08:51:51

GREAT JOB - thanks - there is one minor correction as using the column name in the " as " created a circular reference exception - now it works !!!

try with , instead of ;

select distinct
iif(item1 <= item2 , item1 , item2 ) as mitem1,
iif(item1 <= item2 , item2 , item1 ) as mitem2,
value
from table1

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:56:30

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources