Information générale
Forum:
Microsoft SQL Server
Titre:
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.
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement