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'.
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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?
sam2929Author Commented:
i want to remove dups with subquery
sam2929Author Commented:
X_ASSESSMENT_DT is timestamp how can i covert it to date and then do max
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
What table structure, what columns are part of your dup test?
ste5anSenior DeveloperCommented:
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.
ste5anSenior DeveloperCommented:
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

PortletPaulEE Topic AdvisorCommented:
>>"X_ASSESSMENT_DT is timestamp how can i covert it to date and then do max"

This indicates to me that you are hoping to keep only the "latest" (most recent) records in a table, or perhaps to extract those into some other table.

Unfortunately Microsoft used he word "timestamp" for something that has absolutely nothing to do with time or dates. In more recent time they have converted that term into a "synonym" for ROWVERSION

So, you  need some other way to determine "latest" or "most recent". If this exists then when using ROW_NUMBER() you want to use descending order.

Let's assume there is a LastUpdated column

DECLARE @Sample TABLE ( ID INT, Payload INT, LastUpdated Datetime )

INSERT  INTO @Sample
VALUES 
        ( 1, 1 ,'20150701 12:13:14'),
        ( 2, 2 ,'20150512 12:13:14'),
        ( 3, 3 ,'20150601 12:13:14'),
        ( 4, 3 ,'20150701 12:13:14'),
        ( 5, 3 ,'20150721 12:13:14')

    SELECT  *
    FROM  ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY S.Payload 
                                             ORDER BY S.LastUpdated DESC ) AS RN
               FROM     @Sample S
             ) AS O
    WHERE   O.RN = 1;

Open in new window

| ID | Payload |            LastUpdated | RN |
|----|---------|------------------------|----|
|  1 |       1 | July, 01 2015 12:13:14 |  1 |
|  2 |       2 |  May, 12 2015 12:13:14 |  1 |
|  5 |       3 | July, 21 2015 12:13:14 |  1 |

Open in new window


Stefan's suggestion to use ID may be the closest approximation of "latest" but you would needs DESCending order for that also.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
sam2929, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.