Menu
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
622
The answer is yes, but only in a very certain way. Now, we want to add two more columns—let's call them other1 and other2—but we don't want to add them to the GROUP BY, because we still want only one row per foo in the results.
To insert a new value to the BIT column, use INSERT statement: INSERT INTO table_name (bit_column) VALUES (1); You can also use TRUE and FALSE as the inputs for the BIT columns, SQL Server will automatically convert them as follow: TRUE will be converted to 1.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
In MySQL, when you try to select a column that isn't used in the GROUP BY clause, or in an aggregate function inside the statement, it is not a valid statement according to SQL standard and will cause an error.
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 Shorter way to do this:
IsActive = MAX(0+IsActive) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
