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'.
LEFT OUTER JOIN (select max(XX.X_ASSESSMENT_DT),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'.
ASKER
i want to remove dups with subquery
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sam2929, do you still need help with this question?
E.g.
Open in new window
btw, what do you want to count with the COUNT(*) in the outer query?