How to select multiple columns from a table while ensuring that one specific column doesn't contain duplicate values in sql server?

Question

Table:

x_id---y---z_id------a-------b-------c
1------0----NULL----Blah----Blah---Blah
2------0----NULL----Blah----Blah---Blah
3------10---6-------Blah----Blah---Blah
3------10---5-------Blah----Blah---Blah
3------10---4-------Blah----Blah---Blah
3------10---3-------Blah----Blah---Blah
3------10---2-------Blah----Blah---Blah
3------10---1-------Blah----Blah---Blah
4------0----NULL----Blah----Blah---Blah
5------0----NULL----Blah----Blah---Blah

My Query:

SELECT
    #temp.x_id,
    #temp.y,
    MAX(#temp.z_id) AS z_id
FROM #temp
GROUP BY 
    #temp.x_id

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

Requirement: I want x_id to be unique and i want to select the MAX value of z_id

Expected Output:

x_id---y---z_id------a-------b-------c
1------0----NULL---Blah----Blah-----Blah
2------0----NULL---Blah----Blah-----Blah
3------10---6------Blah----Blah-----Blah
4------0----NULL---Blah----Blah-----Blah
5------0----NULL---Blah----Blah-----Blah

Tim Biegeleisen · Accepted Answer · 2019-09-19 06:22:55Z

2

The error you are seeing is a common one, and it is happening because when you state GROUP BY x_id you are telling SQL Server to return a single record for every value of x_id. However, when you select y, it is unclear as to which one of possibly many values you want to use. Hence, it is resulting in error. One correct approach would be to use ROW_NUMBER:

SELECT TOP 1 WITH TIES x_id, y, z_id, a, b, c
FROM #temp
ORDER BY ROW_NUMBER() OVER (PARTITION BY x_id ORDER BY z_id DESC);

answered Sep 19, 2019 at 6:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select multiple columns from a table while ensuring that one specific column doesn't contain duplicate values in sql server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related