SELECT only the rows with a unique value on certain columns [closed]

Question

I have a table with multiple records by each employee, i need to find all the records by employee where any of the columns values changes. for example i have the following data for George:

HOME	CURRENT	EFFECTIVEDATE	NOTTOEXCEEDDATE	FLAG	GRADE	POSITION	LASTPAYPERIODPAID
GEORGE	610422	610422	NULL	NULL	N	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	610422	NULL	NULL	N	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610124	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610124	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610124	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610124	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	610422	NULL	NULL	N	15	SUPERVISOR ACCOUNTANT
GEORGE	610124	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	610422	NULL	NULL	N	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	15	SUPERVISOR ACCOUNTANT

How do i get the following result data set ?

HOME	CURRENT	EFFECTIVEDATE	NOTTOEXCEEDDATE	FLAG	GRADE	POSITION	LASTPAYPERIODPAID
GEORGE	610124	630100	11/10/2019	11/6/2021	Y	14	ACCOUNTANT
GEORGE	610422	630100	11/10/2019	11/6/2021	Y	15	SUPERVISOR ACCOUNTANT
GEORGE	610422	610422	NULL	NULL	N	15	SUPERVISOR ACCOUNTANT

so far i can only get the result data set by doing a Select Distinct without the LAST_PAY_PERIOD_PAID column. as soon as i add this column i get the first table all over again.

any help is greatly appreciated

Answer 1

I can't see your problem

Distinct produces exactly what you want

SELECT 
DISTINCT
[HOME]
, [CURRENT]
, [EFFECTIVEDATE]
, [NOTTOEXCEEDDATE]
, [FLAG]
, [GRADE]
, [POSITION]
, [LASTPAYPERIODPAID]
FROM Table1

Result

HOME    CURRENT EFFECTIVEDATE   NOTTOEXCEEDDATE     FLAG        GRADE   POSITION    LASTPAYPERIODPAID
GEORGE  610124  630100          11/10/2019          11/6/2021   Y       14          ACCOUNTANT
GEORGE  610422  610422          (null)              (null)      N       15          SUPERVISOR ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       14          ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       15          SUPERVISOR ACCOUNTANT

George has two current numbers, that is why i don't get your result

http://sqlfiddle.com/#!18/f5920/1