How do I modify this query to also return Column D [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add details and clarify the pr开发者_StackOverflowoblem by editing this post.

Closed 8 years ago.

Improve this question

SELECT   ColA, MAX(CONVERT(varchar(15),ColB) + ' ' + CONVERT(varchar(15),ColC))
FROM     dbo.TableX
GROUPBY  ColA

---

SELECT 
    ColA, MAX(CONVERT(varchar(15),ColB) + ' ' + CONVERT(varchar(15),ColC)), 
    ColD 
FROM 
    dbo.TableX 
GROUP BY 
    ColA, ColD

---

RE: Your comment "This causes rows to be returned that have non-unique ColA."

Looking at your previous question I think you need something like

;WITH X AS
(
SELECT   ColA, 
         ColD,
         RANK() OVER (PARTITION BY ColA ORDER BY CONVERT(varchar(15),ColB) + ' ' + CONVERT(varchar(15),ColC) DESC) Rnk
FROM     dbo.TableX
)
SELECT ColA, ColD, Rnk
FROM X
WHERE Rnk = 1;