CODEWITHSUNDEEP
sqlsql-serverselectunionunion-all
Swaggrammer
Swaggrammer

Reputation: 45

SQL Select Max Date after a Union

I want to select the top date (based on my SQL query) out of the three union row records but I am facing with this error:

Column 'tbl1.intDocID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT TOP 1 
    a.YearX, intClientCode
FROM 
    (SELECT intDocID, MAX(dtY1) AS YearX, 1 AS position 
     FROM [tbl1] WHERE intDocID = '834'
     UNION ALL
     SELECT intDocID, dtY2, 2 FROM [tbl1] 
     WHERE intDocID = '834'
     UNION ALL
     SELECT intDocID, dtY3, 3 FROM [tbl1] 
     WHERE intDocID = '834') a
ORDER BY 
    a.YearX DESC

Upvotes: 0

Views: 1270

Answers (1)

Ahmmed
Ahmmed

Reputation: 1048

You need to add GROUP BY clause in your select statement. The GROUP BY statement is require with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns

SELECT TOP 1 
    a.YearX, intClientCode 
FROM
    (SELECT intDocID, MAX(dtY1) AS YearX, 1 AS position FROM [tbl1] 
     WHERE intDocID = '834' 
     GROUP BY intDocID
     UNION ALL
     SELECT intDocID, dtY2, 2 FROM [tbl1] 
     WHERE intDocID = '834' 
     UNION ALL
     SELECT intDocID, dtY3, 3 FROM [tbl1] 
     WHERE intDocID = '834') a

ORDER BY a.YearX desc

Upvotes: 1

Related Questions