CODEWITHSUNDEEP
sql-server-2005
user475464
user475464

Reputation: 1763

Ordering results w/ non-standard ordering (numerically ascending but w/ 0's at the end) & Adding a row number for each row.

SET NOCOUNT ON 

DECLARE @Products TABLE (product_id VarChar(50),product_name VarChar(50) )

INSERT INTO @Products Values ('1','Pen');
INSERT INTO @Products Values ('2','Pencil');
INSERT INTO @Products Values ('3','School Bag');
INSERT INTO @Products Values ('4','Book');
INSERT INTO @Products Values ('5','Pencil Box');

SET NOCOUNT ON 

DECLARE @Rates TABLE (product_id VarChar(50),price int )

INSERT INTO @Rates Values ('1','10');
INSERT INTO @Rates Values ('3','5');
INSERT INTO @Rates Values ('1','5');
INSERT INTO @Rates Values ('4','20');
INSERT INTO @Rates Values ('4','15');
INSERT INTO @Rates Values ('5','30');

SELECT count(*) over() Total_Record,
    p.product_id, p.product_name,  ISNULL(MIN(r.price), 0) AS MinPrice 
FROM 
    @Products p 
LEFT OUTER JOIN
    @Rates r 
ON
    r.product_id = p.product_id 
GROUP BY 
    p.product_id, p.product_name
ORDER BY
    p.product_name

I need some more filtering....

  1. Need MinPrice as ASC and MinPrice'0' comes last in the output list (eg 1, 5,6,10,8,0,0,0... )

  2. ROW_NUMBER() OVER (---) as per the last output...

Upvotes: 0

Views: 104

Answers (1)

Mike Ryan
Mike Ryan

Reputation: 4374

For #1, to create a sort order like this (1, 5, 6, 8, 10, 0, 0, 0), you can create another column that can be sorted upon and embed the special sorting condition you want with that column's definition.

For example (see sortOrder below):

SELECT count(*) over() Total_Record,
    p.product_id, p.product_name,  
    ISNULL(MIN(r.price), 0) AS MinPrice,
    case when ISNULL(MIN(r.price), 0) > 0 then 1 else 0 end as sortOrder
FROM @Products p  
LEFT OUTER JOIN @Rates r  ON
    r.product_id = p.product_id  
GROUP BY 
    p.product_id, p.product_name 
ORDER BY
    sortOrder desc, MinPrice asc

Assuming I understand your question correctly.

I switched your 8 & 10 and also I assume you are trying to sort by the calculated minimum price field ascending, but with the 0's at the end rather than the beginning. All the non-zero values get a sortOrder = 1 and the zero values get a sortOrder = 0. Which separates out the zero and non-zero values, so you can sort within them as you want

As for #2 -- for that one, you want an extra column in your select that includes the row number of ordered values.

The only easy way to do this is via row_number().

SELECT count(*) over() Total_Record,
    p.product_id, p.product_name,  
    ISNULL(MIN(r.price), 0) AS MinPrice,
    case when ISNULL(MIN(r.price), 0) > 0 then 1 else 0 end as sortOrder,
    row_number() OVER(order by sortOrder desc, MinPrice asc) as rowNumber
FROM @Products p  
LEFT OUTER JOIN @Rates r  ON
    r.product_id = p.product_id  
GROUP BY 
    p.product_id, p.product_name 
ORDER BY
    sortOrder desc, MinPrice asc

This creates a row_number() based upon the output order_by clause.

(Note that I don't have a SQL Server instance in front of me right now to double check syntax. Plus, I would really want to check to make sure that MSSQL doesn't balk on combining group by w/ the row_number(). The docs say nothing, and I don't use this function very much. But here it is.)

Upvotes: 1

Related Questions