• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

How can I implement an "order by" on this select statement?

Here's my code that works:

select DP.int_DEVELOPERS_Tool_Id as tool_id
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS	
ON 
DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
WHERE
DP.int_DEVELOPERS_Tool_Id IS NOT NULL
AND 
DP.int_DEVELOPERS_Tool_Id<>0 
AND	
(DPS.int_DEVELOPERS_ProjectStatus_Id =1 OR DPS.int_DEVELOPERS_ProjectStatus_Id=2 OR DPS.int_DEVELOPERS_ProjectStatus_Id=3 OR DPS.int_DEVELOPERS_ProjectStatus_Id=4)
group by DP.int_DEVELOPERS_Tool_Id

Open in new window


The challenge is I need to order each of these tool_id's according to a "project rank" value that's in the  tbl_DEVELOPERS_Projects table, but when I do this:

select DP.int_DEVELOPERS_Tool_Id as tool_id, DP.int_ProjectRank
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS	
ON 
DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
WHERE
DP.int_DEVELOPERS_Tool_Id IS NOT NULL
AND 
DP.int_DEVELOPERS_Tool_Id<>0 
AND	
(DPS.int_DEVELOPERS_ProjectStatus_Id =1 OR DPS.int_DEVELOPERS_ProjectStatus_Id=2 OR DPS.int_DEVELOPERS_ProjectStatus_Id=3 OR DPS.int_DEVELOPERS_ProjectStatus_Id=4)
group by DP.int_DEVELOPERS_Tool_Id
order by DP.int_ProjectRank

Open in new window


I get this message:

Msg 8120, Level 16, State 1, Line 1
Column 'net_BudgetTools..tbl_DEVELOPERS_Projects.int_ProjectRank' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I structure this query where I can get my unique tool_ids and order them according to their corresponding project rank values?
0
brucegust
Asked:
brucegust
  • 5
  • 3
  • 2
  • +3
4 Solutions
 
Walter RitzelSenior Software EngineerCommented:
Since I'm not seeing any reason for that group by, just remove it and it should work.
0
 
Lee SavidgeCommented:
Wrap the select statement in brackets and select and order from that.

select * from (... you sql statement ...) myAliasName order by int_ProjectRank

That should work.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Since you don't have any aggregate function you won't need the GROUP BY clause. Just remove it and the query should run:
SELECT DP.int_DEVELOPERS_Tool_Id as tool_id, DP.int_ProjectRank
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
	LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS	
		ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
WHERE DP.int_DEVELOPERS_Tool_Id IS NOT NULL
	AND DP.int_DEVELOPERS_Tool_Id<>0 
	AND	(DPS.int_DEVELOPERS_ProjectStatus_Id =1 OR DPS.int_DEVELOPERS_ProjectStatus_Id=2 OR DPS.int_DEVELOPERS_ProjectStatus_Id=3 OR DPS.int_DEVELOPERS_ProjectStatus_Id=4)
ORDER BY DP.int_ProjectRank

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
brucegustPHP DeveloperAuthor Commented:
I need the "group by" because I need unique tool_id's.

Sorry for not making that more clear...
0
 
brucegustPHP DeveloperAuthor Commented:
I tried to incorporate the () approach and did this:

select * from (select DP.int_DEVELOPERS_Tool_Id as tool_id, DP.int_ProjectRank
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS	
ON 
DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
WHERE
DP.int_DEVELOPERS_Tool_Id IS NOT NULL
AND 
DP.int_DEVELOPERS_Tool_Id<>0 
AND	
(DPS.int_DEVELOPERS_ProjectStatus_Id =1 OR DPS.int_DEVELOPERS_ProjectStatus_Id=2 OR DPS.int_DEVELOPERS_ProjectStatus_Id=3 OR DPS.int_DEVELOPERS_ProjectStatus_Id=4)
group by DP.int_DEVELOPERS_Tool_Id) 
order by DP.int_ProjectRank

Open in new window


...and I got this error:

"Incorrect syntax near the keyword 'order'"

Where am I blowing it?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I need the "group by" because I need unique tool_id's.
Then tell us which project rank do you need. The first or the last one?
0
 
Walter RitzelSenior Software EngineerCommented:
Use a distinct
SELECT distinct DP.int_DEVELOPERS_Tool_Id as tool_id, DP.int_ProjectRank
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
	LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS	
		ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
WHERE DP.int_DEVELOPERS_Tool_Id IS NOT NULL
	AND DP.int_DEVELOPERS_Tool_Id<>0 
	AND	(DPS.int_DEVELOPERS_ProjectStatus_Id =1 OR DPS.int_DEVELOPERS_ProjectStatus_Id=2 OR DPS.int_DEVELOPERS_ProjectStatus_Id=3 OR DPS.int_DEVELOPERS_ProjectStatus_Id=4) 
ORDER BY DP.int_ProjectRank

Open in new window

0
 
JestersGrindCommented:
There are two ways that should work.  You can remove the parenthesis like this.  

SELECT
	DP.int_DEVELOPERS_Tool_Id AS tool_id,
	DP.int_ProjectRank
FROM
	net_BudgetTools..tbl_DEVELOPERS_Projects DP
	LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS 
		ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND
				DPS.bit_Is_Active = 1
WHERE
	DP.int_DEVELOPERS_Tool_Id IS NOT NULL AND
	DP.int_DEVELOPERS_Tool_Id <> 0 AND
	(DPS.int_DEVELOPERS_ProjectStatus_Id = 1 OR
		DPS.int_DEVELOPERS_ProjectStatus_Id = 2 OR
		DPS.int_DEVELOPERS_ProjectStatus_Id = 3 OR
		DPS.int_DEVELOPERS_ProjectStatus_Id = 4)
GROUP BY
	DP.int_DEVELOPERS_Tool_Id
ORDER BY
    DP.int_ProjectRank

Open in new window


Or if you insist on using the inner subquery, you need to give it an alias like this.

SELECT
    *
FROM
    (
     SELECT
        DP.int_DEVELOPERS_Tool_Id AS tool_id,
        DP.int_ProjectRank
     FROM
        net_BudgetTools..tbl_DEVELOPERS_Projects DP
        LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS 
			ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND
               DPS.bit_Is_Active = 1
     WHERE
        DP.int_DEVELOPERS_Tool_Id IS NOT NULL AND
        DP.int_DEVELOPERS_Tool_Id <> 0 AND
        (DPS.int_DEVELOPERS_ProjectStatus_Id = 1 OR
         DPS.int_DEVELOPERS_ProjectStatus_Id = 2 OR
         DPS.int_DEVELOPERS_ProjectStatus_Id = 3 OR
         DPS.int_DEVELOPERS_ProjectStatus_Id = 4)
     GROUP BY
        DP.int_DEVELOPERS_Tool_Id
    ) AS A
ORDER BY
    A.int_ProjectRank

Open in new window


Hope this helps!

Greg
0
 
brucegustPHP DeveloperAuthor Commented:
Greg, when I use the "inner subquery" I get this error:

Msg 8120, Level 16, State 1, Line 8
Column 'net_BudgetTools..tbl_DEVELOPERS_Projects.int_ProjectRank' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


...and when I use your first suggestion, I get this:

Msg 8120, Level 16, State 1, Line 3
Column 'net_BudgetTools..tbl_DEVELOPERS_Projects.int_ProjectRank' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Not sure how to get around that. Any ideas?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, I'll guess that you want the last ProjectRank returned, so try this one:
SELECT DP.int_DEVELOPERS_Tool_Id as tool_id, MAX(DP.int_ProjectRank)
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
	LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS	
		ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
WHERE DP.int_DEVELOPERS_Tool_Id IS NOT NULL
	AND DP.int_DEVELOPERS_Tool_Id<>0 
	AND	(DPS.int_DEVELOPERS_ProjectStatus_Id =1 OR DPS.int_DEVELOPERS_ProjectStatus_Id=2 OR DPS.int_DEVELOPERS_ProjectStatus_Id=3 OR DPS.int_DEVELOPERS_ProjectStatus_Id=4)
GROUP BY DP.int_ProjectRank
ORDER BY DP.int_ProjectRank

Open in new window

0
 
Anthony PerkinsCommented:
Vitor Montalvão,

I suspect you intended to say:
SELECT  DP.int_DEVELOPERS_Tool_Id AS tool_id,
        MAX(DP.int_ProjectRank) int_ProjectRank
FROM    net_BudgetTools..tbl_DEVELOPERS_Projects DP
        LEFT JOIN net_BudgetTools..tbl_DEVELOPERS_ProjectStatuses DPS ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id
                                                                         AND DPS.bit_Is_Active = 1
WHERE   DP.int_DEVELOPERS_Tool_Id IS NOT NULL
        AND DP.int_DEVELOPERS_Tool_Id <> 0
        AND DPS.int_DEVELOPERS_ProjectStatus_Id IN (1, 2, 3, 4)
GROUP BY DP.int_DEVELOPERS_Tool_Id
ORDER BY int_ProjectRank

Open in new window


But I agree it does not make much sense.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thanks Anthony. I didn't even see the WHERE clause. Was more focused on the SELECT columns and GROUP BY.
0
 
Anthony PerkinsCommented:
I didn't even see the WHERE clause. Was more focused on the SELECT columns and GROUP BY.
I was not concerned about the WHERE clause (that was just me automatically abbreviating) I was more concerned with the fact that SELECT items did not match the GROUP BY.

In other words you cannot have:
SELECT DP.int_DEVELOPERS_Tool_Id as tool_id, MAX(DP.int_ProjectRank)
...
GROUP BY DP.int_ProjectRank
ORDER BY DP.int_ProjectRank

If you are going to have DP.int_DEVELOPERS_Tool_Id in the SELECT it has to be in an aggregate function or in the GROUP BY.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh dear. The GROUP BY also. Looks like I need an appointment with the ophthalmologist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now