CODEWITHSUNDEEP
sql-servert-sql
joerdie
joerdie

Reputation: 379

t sql Group By clause not having intended effect

I am having some trouble with what I believe is a group by clause. I am trying to display a distinct CompanyID with the earliest InstallDate Here is my code:

SELECT
Pa.PARTY_ID
,MIN(SM.[INSTALLDATE]) INSTALLDATE
,SM.[STOPDATE]
FROM FORMDESC FD
INNER JOIN STATEINFO SI
    ON FD.FORMDESCID = SI.FORMDESCID
INNER JOIN COMPANY C
    ON FD.COMPANYID = C.COMPANYID
INNER JOIN PARTY Pa
    ON C.COMPANYFULLNAME = Pa.FULL_NM
INNER JOIN STATEMARKET SM
    ON SI.STATEINFOID = SM.STATEINFOID
WHERE ENTIREFORMNUMBER = 'ABC123'
GROUP BY PARTY_ID, INSTALLDATE, STOPDATE

My current output is:

CompanyID | INSTALLDATE         | STOPDATE
-------------------------------------------
60        | NULL                | NULL
61        | 2015-09-26 00:00:00 | NULL
62        | NULL                | NULL
62        | 2014-09-29 00:00:00 | NULL
62        | 2016-04-15 00:00:00 | NULL

The output I am seeking is:

CompanyID   | INSTALLDATE           | STOPDATE
----------------------------------------
60          | NULL                  | NULL
61          | 2015-09-26 00:00:00   | NULL
62          | 2014-09-29 00:00:00   | NULL

A left join on the SM table will not help me here either. If I pull any of the values from my group by clause, I get an error. Is this a case where I should break this into a couple of selects instead?

While James Z's answer would indeed work; it was pointed out to me that my group by contained INSTALLDATE which caused my issue.

Upvotes: 0

Views: 31

Answers (1)

James Z
James Z

Reputation: 12317

You probably need something like this:

select * from (
    SELECT
    Pa.PARTY_ID
    ,SM.[INSTALLDATE]
    ,SM.[STOPDATE]
    ,row_number() over (partition by Pa.PARTY_ID order by SM.[INSTALLDATE] desc) as RN
    FROM FORMDESC FD
    INNER JOIN STATEINFO SI
        ON FD.FORMDESCID = SI.FORMDESCID
    INNER JOIN COMPANY C
        ON FD.COMPANYID = C.COMPANYID
    INNER JOIN PARTY Pa
        ON C.COMPANYFULLNAME = Pa.FULL_NM
    INNER JOIN STATEMARKET SM
        ON SI.STATEINFOID = SM.STATEINFOID
    WHERE ENTIREFORMNUMBER = 'ABC123'
) X
where RN = 1

This will number the rows separately for each party_id, and then show the earliest one of them. This way you can get other data from the same row where the minimum value exists.

Upvotes: 1

Related Questions