SELECT DISTINCT - 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

SELECT DISTINCT

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-02-21 : 08:04:20

Good Morning Folks..

I have written a script that will be selecting various records from various tables all based on the CLAIMANT_NUMBER from the CLAIM file.. What I am trying to do is select only distinct CLAIMANT_NUMBER.. I can run a script to create a temp file and than use that but I was wondering if I can do some thing like this

SELECT

C.CLAIMANT_NUMBER,C.CLAIMANT_NAME
FROM CLAIM C

INNER JOIN CLIENT C ON C.CLAIMANT_NUMBER=C.CLIENT_NUMBER
INNER JOIN ADDRESS A2 ON C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER
' ' OR A2.ADDRESS1 IS NOT NULL)
AND A2.SEQUENCE_NUMBER = (SELECT MAX (SEQUENCE_NUMBER) FROM ADDRESS A4 WHERE A2.CLIENT_NUMBER = A4.CLIENT_NUMBER)
A3.POLICY_NUMBER AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME
WHERE (SELECT DISTINCT C.CLAIMANT_NUMBER FROM CLAIM C)

This is not working so i need help and I hope you get the idea what I am trying to do..

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-02-21 : 08:10:12

[code]
SELECT

DISTINCT C.CLAIMANT_NUMBER

FROM CLAIM C

INNER JOIN CLIENT C ON C.CLAIMANT_NUMBER=C.CLIENT_NUMBER
INNER JOIN ADDRESS A2 ON C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER
' ' OR A2.ADDRESS1 IS NOT NULL)
AND A2.SEQUENCE_NUMBER = (SELECT MAX (SEQUENCE_NUMBER) FROM ADDRESS A4 WHERE A2.CLIENT_NUMBER = A4.CLIENT_NUMBER)
A3.POLICY_NUMBER AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME
WHERE (SELECT DISTINCT C.CLAIMANT_NUMBER FROM CLAIM C)
[/code]

divan
Posting Yak Master

153 Posts

Posted - 2013-02-21 : 08:13:18

Have tried this and have been getting this error message

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 08:23:28

The following will parse without any syntax errors, but read through the join conditions to see if that is what you need

SELECT C.CLAIMANT_NUMBER,
       C.CLAIMANT_NAME
FROM   CLAIM C
       INNER JOIN CLIENT C
            ON  C.CLAIMANT_NUMBER = C.CLIENT_NUMBER
       INNER JOIN ADDRESS A2
            ON  (
                    C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER
                    OR A2.ADDRESS1 IS NOT NULL
                )
            AND A2.SEQUENCE_NUMBER = (
                    SELECT MAX(SEQUENCE_NUMBER)
                    FROM   ADDRESS A4
                    WHERE  A2.CLIENT_NUMBER = A4.CLIENT_NUMBER
                )
            AND A3.POLICY_NUMBER = 'SOMETHING HERE'
            AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME
--NOT SURE WHAT THIS WHERE CLAUSE IS FOR
--WHERE  (
--           SELECT DISTINCT C.CLAIMANT_NUMBER
--           FROM   CLAIM C
--       )

divan
Posting Yak Master

153 Posts

Posted - 2013-02-21 : 08:31:12

The WHERE clause is trying to be used to only select distinct CLAIMANT_NUMBER from the CLAIM table

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 08:34:14

You can put Distinct in SELECT clause only


SELECT DISTINCT C.CLAIMANT_NUMBER,
       C.CLAIMANT_NAME
FROM   CLAIM C
       INNER JOIN CLIENT C
            ON  C.CLAIMANT_NUMBER = C.CLIENT_NUMBER
       INNER JOIN ADDRESS A2
            ON  (
                    C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER
                    OR A2.ADDRESS1 IS NOT NULL
                )
            AND A2.SEQUENCE_NUMBER = (
                    SELECT MAX(SEQUENCE_NUMBER)
                    FROM   ADDRESS A4
                    WHERE  A2.CLIENT_NUMBER = A4.CLIENT_NUMBER
                )
            AND A3.POLICY_NUMBER = 'SOMETHING HERE'
            AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources