General information
Forum:
Microsoft SQL Server
Title:
Trouble with GROUP BY and ORDER BY
Hi,
In the following query, I need to order by costdate desc and starttime desc, but I only want to group by ljob and id, which you cannot do because starttime and costdate are in the SELECT
DECLARE @tcEmpno char(9)
SET @tcEmpno = '000000298'
SELECT jobinfo.ljob, COUNT(jobinfo.ljob) AS Weight, jobinfo.costdate, jobinfo.starttime,
CASE WHEN bbjthead.id = '' THEN '(No-ID)' ELSE bbjthead.id END AS ID FROM
(
SELECT ljob, costdate, starttime FROM bbjobcst
WHERE ljob <> ' ' AND ljob <> 'None ' AND costdate >= GETDATE() - 15 AND empno = @tcEmpno
--ORDER BY costdate desc, starttime desc
UNION ALL
SELECT ljob, costdate, starttime
FROM bbmatdc
WHERE ljob <> ' ' AND ljob <> 'None ' AND
costdate >= GETDATE() - 15 AND empno = @tcEmpno
--ORDER BY costdate desc, starttime desc
UNION ALL
SELECT ljob, costdate, starttime
FROM bbrolldc
WHERE ljob <> '' AND ljob <> 'None ' AND costdate >= GETDATE() - 15 AND empno = @tcEmpno
--ORDER BY costdate desc, starttime desc
)jobinfo
INNER JOIN BBJTHEAD ON BBJTHEAD.LJOB = jobinfo.ljob
GROUP BY jobinfo.ljob, BBJTHEAD.ID, jobinfo.costdate, jobinfo.starttime
ORDER BY jobinfo.costdate desc, jobinfo.starttime desc
Does anyone have any ideas on how I can accomplish this???
Thanks in advance.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only