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?
Bruce GustPHP DeveloperAsked:
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.

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Sorry for not making that more clear...
0
Bruce GustPHP 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

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
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
Bruce GustPHP 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
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.