help with returning distinct rows with multi field

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)

help with returning distinct rows with multi field

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-24 : 17:51:23

selecting distinct sopnumbe in this query

select a.sopnumbe,
b.itemnmbr,
b.itemdesc,
a.bachnumb,
a.invodate,
a.cstponbr,
a.duedate,
b.quantity,
b.unitprce,
c.cmmttext, /* txt field */
a.locncode,
b.DEX_ROW_ID
from dbo.boa a left join dbo.lines_vw b on a.sopnumbe = b.sopnumbe left join dbo.boa_vw c on c.sopnumbe = b.sopnumbe
and b.soptype = c.soptype

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-24 : 18:28:55

I'm not sure I understand your question... Are you just trying to select a distinct a.sopnumbe? If so, then just use the DISTINCT keyword:

select DISTINCT a.sopnumbe,
b.itemnmbr,
b.itemdesc,
a.bachnumb,
a.invodate,
a.cstponbr,
a.duedate,
b.quantity,
b.unitprce,
c.cmmttext, /* txt field */
a.locncode,
b.DEX_ROW_ID
from dbo.boa a left join dbo.lines_vw b on a.sopnumbe = b.sopnumbe left join dbo.boa_vw c on c.sopnumbe = b.sopnumbe
and b.soptype = c.soptype

Some days you're the dog, and some days you're the fire hydrant.

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-25 : 09:06:54

I get this error due to the text data type on cmmttext column

"Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable."

quote:
Originally posted by Skorch

I'm not sure I understand your question... Are you just trying to select a distinct a.sopnumbe? If so, then just use the DISTINCT keyword:

select DISTINCT a.sopnumbe,
b.itemnmbr,
b.itemdesc,
a.bachnumb,
a.invodate,
a.cstponbr,
a.duedate,
b.quantity,
b.unitprce,
c.cmmttext, /* txt field */
a.locncode,
b.DEX_ROW_ID
from dbo.boa a left join dbo.lines_vw b on a.sopnumbe = b.sopnumbe left join dbo.boa_vw c on c.sopnumbe = b.sopnumbe
and b.soptype = c.soptype

Some days you're the dog, and some days you're the fire hydrant.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 09:09:23

You only want the distinct sopnumbe or distinct of all the selection column list ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 09:09:24

select DISTINCT a.sopnumbe,
b.itemnmbr,
b.itemdesc,
a.bachnumb,
a.invodate,
a.cstponbr,
a.duedate,
b.quantity,
b.unitprce,
CAST(c.cmmttext AS VARCHAR(MAX)), /* txt field */
a.locncode,
b.DEX_ROW_ID
from dbo.boa a left join dbo.lines_vw b on a.sopnumbe = b.sopnumbe left join dbo.boa_vw c on c.sopnumbe = b.sopnumbe
and b.soptype = c.soptype

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

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-25 : 10:45:01

just sopnumbe

thanks

quote:
Originally posted by khtan

You only want the distinct sopnumbe or distinct of all the selection column list ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 13:56:19

select distinct a.sopnumbe,
from dbo.boa a left join dbo.lines_vw b on a.sopnumbe = b.sopnumbe left join dbo.boa_vw c on c.sopnumbe = b.sopnumbe
and b.soptype = c.soptype

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:00:25

quote:
Originally posted by Peso

select distinct a.sopnumbe,
from dbo.boa a left join dbo.lines_vw b on a.sopnumbe = b.sopnumbe left join dbo.boa_vw c on c.sopnumbe = b.sopnumbe
and b.soptype = c.soptype

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

small typo fixed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources