Custom GROUP BY clause

Question

SAMPLE DATA

Suppose I have table like this:

No    Company     Vendor    Code   Date
1     C1          V1        C1     2016-03-08  
1     C1          V1        C1     2016-03-07
1     C1          V1        C2     2016-03-06

DESIRED OUPUT

Desired output should be:

No    Company     Vendor    Code   Date
1     C1          V1        C1     2016-03-08

It should take max Date for No, Company, Vendor (group by these columns). But shouldn't group by Code, It have to be taken for that Date.

QUERY

SQL query like:

.....

LEFT JOIN (
    SELECT No_, Company, Vendor, Code, MAX(Date)
    FROM tbl 
    GROUP BY No_, Company, Vendor, Code
) t2 ON t1.Company = t2.Company and t1.No_ = t2.No_

.....

OUTPUT FOR NOW

But I got output for now:

No    Company     Vendor    Code   Date
1     C1          V1        C1     2016-03-08  
1     C1          V1        C2     2016-03-06

That because Code records are different, but It should take C1 code in this case (because No, Company, Vendor match)

WHAT I'VE TRIED

I've tried to remove Code from GROUP BY clause and use SELECT MAX(Code)..., but this is wrong that because It take higher Code by alphabetic.

Have you ideas how can I achieve It? If something not clear I can explain more.

why it should take `c1` and not `c2` ? you don't tell us .. Do you need the `Code` ? — Blag, Mar 18 '16 at 08:25
how about - `where code = top 1 code from .....` is it what you want? — j.kaspar, Mar 18 '16 at 08:28
@Blag that because `Code = c1` with max `Date` for `No, Company, Vendor`. It should have Group by `No, Company, Vendor`, take max `Date` and record of `Code` of that row where max `Date. Hope you understand... — Infinity, Mar 18 '16 at 08:45

score 4 · Accepted Answer · edited May 23 '17 at 12:31

If you don't have any identity column for your table then each row is identified by all column values combination it has. That brings us weird on statement. It includes all columns we are grouping by and a date column which is max for given tuple (No_, Company, Vendor).

select t1.No_, t1.Company, t1.Vendor, t1.Code, t1.Date 
from tbl t1 
join (select No_, Company, Vendor, MAX(Date) as Date 
    from tbl 
    group by No_, Company, Vendor) t2
on t1.No_ = t2.No_ and 
   t1.Company = t2.Company and 
   t1.Vendor = t2.Vendor and 
   t1.Date = t2.Date

Take a look at this similar question.

Edit

Thank you for an answer, but this returning duplicates. Suppose that there can be rows with equal No, Company, Vendor and Date, some other columns are different, but no care. So with INNER SELECT everything fine, It returning distinct values, but problem accured when joining t1, that because It have multiple values.

Then you might be interested in such tsql constructions as rank or row_number. Take a look at Ullas' answer. Try rank as well as it can give slightly different output which might fit your needs.

Thank you for an answer, but this returning duplicates. Suppose that there can be rows with equal `No, Company, Vendor and Date`, some other columns are different, but no care. So with `INNER SELECT` everything fine, It returning distinct values, but problem accured when joining `t1`, that because It have multiple equal rows. I've tried to use `SELECT DISTINCT`, but still have multiple `Codes` returned. — Infinity, Mar 18 '16 at 09:09
@Infinity, what if you have multiple equal row, do you want all of them in the output? What if have two equal rows but with different `Code` do you want both of them in the output? — Ivan Gritsenko, Mar 18 '16 at 09:11

Mark · Answer 2 · 2016-03-18T09:18:09.537

2

If 1 date only can have 1 record, then you can Query it by search the max date first, then check it.

select No_, Company, Vendor, Code, Date
FROM tbl
where Date in
    (select MAX(Date) from tbl GROUP BY No_, Company, Vendor)

if there is more than 1 row that could have the same date, then you could use partition

with cte as
(
select *, ROW_NUMBER() over(partition by No_, Company, Vendor order by Date DESC) as rn
from tbl
)
select No_, Company, Vendor, Code, Date
from cte
where rn=1

edited Mar 18 '16 at 09:18

answered Mar 18 '16 at 08:29

Mark

2,041
2
18
35

Reading the question before answering is not optional please... ; this do not solve the "why `C1` and not `C2` in CODE" problem. and as the OP don't tell us about this, we can't guess it. – Blag Mar 18 '16 at 08:33
@Blag yes, he wants the record that is grouped by No, Company, Vendor. then choose the max date within it. Any comments? – Mark Mar 18 '16 at 08:35
@Blag Please read the question carefully. OP does MENTION that because `C1` and `C2` have the same no, company, and match. thats why it should show 1 record only rather than 2. and he shows C1 cause C1 has a newer date than C2 – Mark Mar 18 '16 at 08:37
@Mark, this not worked, have many duplicates. There can be more than 1 record for the same date, but with different vendors or companies... – Infinity Mar 18 '16 at 09:00

score 2 · Answer 3 · answered Mar 18 '16 at 08:45

You could give a row_number partitioned by No, Vendor and Date and order by descending order of date.

Query

;with cte as (
    select rn = row_number() over(
        partition by [No], Company, Vendor
        order by [Date] desc
    ), *
    from tbl
)
select [No], Company, Vendor, Code, [Date] from cte
where rn = 1;

score 1 · Answer 4 · answered Mar 18 '16 at 08:59

A Common Table Expression will do it for you.

WITH cte(N,C,V,D)
AS
(
SELECT t1.[No]
      ,t1.[Company]
      ,t1.[Vendor]
      ,MAX(t1.[Date])
  FROM [MyTest] t1
  GROUP BY t1.[No]
      ,t1.[Company]
      ,t1.[Vendor]
 )
 SELECT N,C,V,t2.Code,D 
 FROM cte c
 INNER JOIN MyTest t2 ON c.N = t2.No AND c.C = t2.Company AND c.V = t2.Vendor AND c.D = t2.Date

Custom GROUP BY clause

4 Answers4