Get unique column value based on a returned list of rows

peter

4y925SQL

I have the following SQL query that at present returns duplicate [Fuel] column values.While i want unique Fuel values to bind to control.

The additional columns are necessary additions for the search function later.

i.e Petrol,Diesel,Electric

I tried to use the Group By instead of using DISTINCT but it would not allow to Group on only one column i.e Fuel and when applied to all columns it returned distinct row with duplicate Fuel values. If anyone knows how to return only unique rows based upon Fuel types that would work for my application.

Thanks

SELECT DISTINCT [CategoryID],Products.[Fuel],[Brand],[ProductSize] FROM [Products] WHERE ([CategoryID] = @CategoryID AND Products.[Category] = @Category) and Products.Fuel is not null

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
you cab use aggregate function in such situations
SELECT Max([CategoryID]),Products.[Fuel],Max([Brand]),Max([ProductSize])FROM [Products] WHERE ([CategoryID] = @CategoryID AND Products.[Category] = @Category) and Products.Fuel is not null
group by Products.[Fuel]
0
peter4y ago
Thanks

That does create unique rows. I did have a problem when selecting item value. i think it was because of extra space in string,so no match was found?

If i expanded the WHERE clause statement such as:

WHERE (Type=@Type and/or CATEGORY=@CATEGORY) and Fuel=@Fuel

What is the correct code to retrieve data based upon conditions: TYPE and CATEGORY is nothing or else

CATEGORY = Value and TYPE nothing or CATEGORY nothing and TYPE = value.

Without throwing an error message when null value?

I dont want to produce multiple query statements or each case as i have others similar. So if i can do it on one single query line that would be better.
0