How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?

Question

Here is my T-SQL query

SELECT 
    ProductID,
    VendorID,
    ProductName= MAX(ProductName),
    VendorName = MAX(VendorName),
    IsActive = MAX(IsActive) # This brings error 
FROM ProductVendorAssoc 
GROUP BY  
    ProductID,
    VendorID

I want to apply GROUP BY only for the ProductID and VendorID fields, but need to populate the ProductID, VendorID, ProductName, VendorName, IsActive fields.

Here I used the agreggate function MAX(ProductName) to avoid ProductName in the group by list.

But the same trick is not working for BIT columns as operand data type bit is invalid for max operator.

How can i include BIT type column in SELECT part with out including it on the GROUP BY?

Update.

What should I need to do if i need to include an INT column like UserID in SELECT in the same way

It is SQL Server 2008, but i need to wok it on 2005 too – Mithun Sreedharan May 19 '11 at 04:50 — Mithun Sreedharan, May 19 '11 at 04:50
@marc_s i omitted that part of query from the question – Mithun Sreedharan May 19 '11 at 04:53 — Mithun Sreedharan, May 19 '11 at 04:53

Damien_The_Unbeliever · Accepted Answer · 2011-05-19T04:50:18.110

113

Put a CASE expression in there, or convert it to int:

IsActive = MAX(CASE WHEN IsActive=1 THEN 1 ELSE 0 END)

or,

IsActive = MAX(CONVERT(int,IsActive))

You should also be aware, obviously, that this means that the values in the ProductName, VendorName and IsActive columns in the result set may all come from different rows in the base table.

If you want those three columns to actually all be from the same row (and assuming SQL Server 2005 or later), you'd do something like:

;With Numbered as (
    SELECT *,ROW_NUMBER() OVER (
        PARTITION BY ProductID,VendorID
        ORDER BY /* Something appropriate, or if we just want random... */ newid()) as rn
    FROM ProductVendorAssoc
)
select
    ProductID,
    VendorID,
    ProductName,
    VendorName,
    IsActive
FROM Numbered where rn=1

edited May 19 '11 at 04:50

answered May 19 '11 at 04:43

Damien_The_Unbeliever

234,701
27
340
448

What should I need to do if i need to include an INT column like UserID in SELECT in the same way – Mithun Sreedharan May 19 '11 at 05:06
@Mithun P - just the same, you can take the `MAX` on that column (with the same disclaimer), or switch to the other style of statement I've shown. – Damien_The_Unbeliever May 19 '11 at 05:50
should the line `SELECT *,ROW_NUMBER() OVER (` have "`rn = `" added to it? Like this: `SELECT *,rn = ROW_NUMBER() OVER (` – Will Newton Aug 26 '14 at 19:13
@WillNewton - there's two ways to associate an alias - `alias = expression` or `expression as alias` - I've used the second form, but it's scrolled over to the right. – Damien_The_Unbeliever Aug 27 '14 at 05:33
How about Indexed views? As we cannot use MAX() in the indexed views, is there a way to handle it in the indexed views? – Hari Krishna Gaddipati May 13 '19 at 17:12
@HariKrishnaGaddipati - in the indexed view, have a `SUM` and `COUNT_BIG`, then wrap a non-indexed view atop that that checks that the sum isn't `0`, I *think*. – Damien_The_Unbeliever May 13 '19 at 17:55

score 15 · Answer 2 · answered Nov 12 '15 at 21:17

15

Shorter way to do this:

IsActive = MAX(0+IsActive)

answered Nov 12 '15 at 21:17

Mike Keskinov

11,614
6
59
87

Implicit type conversion? – FranTastic Mar 03 '21 at 11:48

score 4 · Answer 3 · answered May 19 '11 at 04:50

In SQL2005/2008 this would be look like this:

select ProductId, VendorId, ProductName, VendorName, IsActive
from
(
    select *, row_number() over (partition by ProductId, VendorId order by ProductId) RowNumber
    from Production.Product
) tt
where RowNumber = 1

How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?

3 Answers3