Is this impossible? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Is this impossible?

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2008-06-23 : 12:57:36

I'm trying to specify an order by on a select without including it in the group by (right now it gives me the "Column 'd_cadd.line1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." message).....is there any way to do this or is it impossible?


SELECT  location_id
INTO    #login_locations
FROM    view_login_locations
WHERE   login_id = 348 CREATE UNIQUE clustered INDEX #login_locations_pk ON #login_locations (location_id)
SELECT  key_id          ,
        can_read_streams,
        linked_expression_id
FROM
        (SELECT top 10000 "d_doc"."document_id" AS key_id,
                CASE
                        WHEN "d_l"."login_id" IS NOT NULL
                        THEN 1
                        ELSE 0
                END  AS can_read_streams,
                NULL AS linked_expression_id
        FROM    #login_locations d_loginlocations
                INNER JOIN documents d_doc
                ON      d_loginlocations.location_id = d_doc.location_id
                INNER JOIN view_folded_document_types d_fdt
                ON      d_doc.document_type_id = d_fdt.document_type_id
                LEFT OUTER JOIN view_login_document_types d_l
                ON      d_doc.document_type_id = d_l.document_type_id
                    AND 348                    = d_l.login_id
                LEFT OUTER JOIN accounts d_ac
                ON      d_doc.account_id = d_ac.account_id
                LEFT OUTER JOIN customer_accounts d_customer_accounts
                ON      d_ac.account_id = d_customer_accounts.account_id
                LEFT OUTER JOIN customers d_cust
                ON      d_customer_accounts.customer_id = d_cust.customer_id
                LEFT OUTER JOIN view_customer_primary_addresses d_cadd
                ON      d_cust.customer_id = d_cadd.customer_id
        WHERE "d_doc"."status"            IS NULL
            AND (("d_fdt"."classification" = isnull('Document', ''))
            AND (1                         =
                CASE
                        WHEN "d_fdt"."level_2" LIKE '%'+isnull('~~~', '')+'%'
                        THEN 0
                        ELSE 1
                END)
            AND (1 =
                CASE
                        WHEN "d_fdt"."level_3" LIKE '%'+isnull('~~~', '')+'%'
                        THEN 0
                        ELSE 1
                END)
            AND (1 =
                CASE
                        WHEN "d_fdt"."level_4" LIKE '%'+isnull('~~~', '')+'%'
                        THEN 0
                        ELSE 1
                END))
        GROUP BY "d_doc"."document_id",
                d_l.login_id    
        ORDER BY "d_cadd"."line1" ASC

        ) AS results OPTION (maxdop 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 13:04:42

Since your grouping by other fields you need to either apply an aggregate function like MIN,MAX ... on "d_cadd"."line1" or include it in GROUP BY as well.

label
Posting Yak Master

197 Posts

Posted - 2008-06-23 : 13:12:41

quote:
Originally posted by visakh16

Since your grouping by other fields you need to either apply an aggregate function like MIN,MAX ... on "d_cadd"."line1" or include it in GROUP BY as well.

Understood, but putting in the Group By is not a possibilty so is there any way around this and still keep the order by ?

I also tried adding this min but it still threw the same error....


SELECT  location_id
INTO    #login_locations
FROM    view_login_locations
WHERE   login_id = 348 CREATE UNIQUE clustered INDEX #login_locations_pk ON #login_locations (location_id)
SELECT  key_id          ,
        can_read_streams,
        linked_expression_id
FROM
        (SELECT top 10000 "d_doc"."document_id" AS key_id,
                CASE
                        WHEN "d_l"."login_id" IS NOT NULL
                        THEN 1
                        ELSE 0
                END  AS can_read_streams,
                NULL AS linked_expression_id, 
			 max("d_cadd"."line1") as bogey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 13:18:31

quote:
Originally posted by label

quote:
Originally posted by visakh16

Since your grouping by other fields you need to either apply an aggregate function like MIN,MAX ... on "d_cadd"."line1" or include it in GROUP BY as well.

Understood, but putting in the Group By is not a possibilty so is there any way around this and still keep the order by ?

I also tried adding this min but it still threw the same error....

SELECT location_id
INTO #login_locations
FROM view_login_locations
WHERE login_id = 348 CREATE UNIQUE clustered INDEX #login_locations_pk ON #login_locations (location_id)
SELECT key_id ,
can_read_streams,
linked_expression_id
FROM
(SELECT top 10000 "d_doc"."document_id" AS key_id,
CASE
WHEN "d_l"."login_id" IS NOT NULL
THEN 1
ELSE 0
END AS can_read_streams,
NULL AS linked_expression_id,
max("d_cadd"."line1") as bogey

Another possibility is to get the line field and other field in a seperate subquery and then join it with main query. If you need more help please opost some sample data from your table and also output that you're expecting from them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources