SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with distinct rows and dynamic order by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

newbie999
Starting Member

Sweden
3 Posts

Posted - 09/30/2013 :  15:02:49  Show Profile  Reply with Quote
Hi

I have a test webpage where you search and filter products from adventureworks2012.
Im currently working/testing this with sql server 2012, but the real case is sql server 2005.

Since I need to filter out products from other tables I have to use outer joins to make sure all products are available for filtering.
And I also need to make sure the user can choose how to order by the result.
In this example I have joined two tables, and I only have a couple of search criterias, but it can be more tables and columns to filter by.

In the result set, I dont want to have duplicate products, so therefore I use group by on productId.
This sql code worked fine until I added the dynamic order by.

For every column I'd like to let the user order by, I have to add it to the group by, So now I have multiple products in my result set.

Change from group by ProductId to select distinct ProductId, might work.
But the column that is not chosen, will return null values, that is not good if I have 10+ different columns to order by.

The two varaibles @safetystocklevel and @vendorId are two examples where the user is filtering.

This code return to many products:

declare @sortType nvarchar(50)
set @sortType  = 'ProductId'
declare @safetystocklevel int
set @safetystocklevel  = 500
declare @vendorId int
set @vendorId  = 1494
select p.ProductID,
CASE WHEN @sortType = 'Status' THEN poh.Status END as Status,
CASE WHEN @sortType = 'ProductId' THEN p.ProductID END as ProductId
from Production.Product p
left outer join Purchasing.PurchaseOrderDetail pod on p.ProductID = pod.ProductID
left outer join Purchasing.PurchaseOrderHeader poh on poh.PurchaseOrderID = pod.PurchaseOrderID
left outer join Production.ProductDocument ppd on ppd.ProductID = p.ProductID
left outer join Production.Document pd on pd.DocumentNode = ppd.DocumentNode
WHERE 
(@safetystocklevel = '' or p.SafetyStockLevel = @safetystocklevel)
and (@vendorId = '' or poh.VendorID = @vendorId)
group by p.ProductID, poh.Status
ORDER BY
    CASE WHEN @sortType = 'Status' THEN poh.Status END ASC,
    CASE WHEN @sortType = 'ProductId' THEN p.ProductID END ASC


What do you think?
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000