Link to home
Start Free TrialLog in
Avatar of sam2929
sam2929

asked on

Msg 8155 error

SELECT COUNT(*),BB.ID FROM  TEST1 B
LEFT OUTER JOIN (select max(XX.X_ASSESSMENT_DT),XX.ID AS ID FROM   TEST1 XX
GROUP BY XX.ID) BB
ON B.ROW_ID = BB.ID

i AM GETTING ERROR

Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 1 of 'BB'.
Avatar of ste5an
ste5an
Flag of Germany image

Your sub-query does not provide a column name for the aggregate.

E.g.

SELECT  COUNT(*) ,
        BB.ID
FROM    TEST1 B
        LEFT OUTER JOIN ( SELECT    MAX(XX.X_ASSESSMENT_DT) AS MAX_X_ASSESSMENT_DT,
                                    XX.ID AS ID
                          FROM      TEST1 XX
                          GROUP BY  XX.ID
                        ) BB ON B.ROW_ID = BB.ID;

Open in new window


btw, what do you want to count with the COUNT(*) in the outer query?
Avatar of sam2929
sam2929

ASKER

i want to remove dups with subquery
Avatar of sam2929

ASKER

X_ASSESSMENT_DT is timestamp how can i covert it to date and then do max
What table structure, what columns are part of your dup test?
You cannot convert a TIMESTAMP to a DATE, cause TIMESTAMP is an internal binary, database wide counter. It's not a date nor date/time value.

Post your table DDL.
E.g. using the ROW_NUMBER() function:

DECLARE @Sample TABLE ( ID INT, Payload INT );

INSERT  INTO @Sample
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 ),
        ( 4, 3 ),
        ( 5, 3 );

WITH    Ordered
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY S.Payload ORDER BY S.ID ASC ) AS RN
               FROM     @Sample S
             )
    SELECT  *
    FROM    Ordered O
    WHERE   O.RN = 1;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sam2929, do you still need help with this question?