CODEWITHSUNDEEP
sqlsql-serverlinq
Ronjon
Ronjon

Reputation: 1849

Sql Select Statement Issue in order by

I have a set of records sorted (Order BY) ProductDesc, RecDate, then ProductBrand and it gives me the below result.

-->> SQL Is 

SELECT RecDate, ProductDesc,ProductBrand
FROM MyTable i
Order BY i.ProductDesc, i.RecDate,i. ProductBrand

--

RecDate     ---     ProductDesc                ---       Brand
--------------------------------------------------------------
2017-07-15      ---     TOMATO ROMA              ---      (S & S)
2017-07-16      ---     TOMATO ROMA              ---      (A & B)
2017-07-17      ---     TOMATO ROMA              ---      (C & D)
2017-07-18      ---     TOMATO ROMA              ---      (AAA)
2017-07-25      ---     TOMATO ROMA              ---      (C & D)
2017-07-26      ---     TOMATO ROMA              ---      (C & D)

but my requirement is to display these records sorted by ProductDesc, RecDate in ASC and then if there is the same product with the same brand (Like 'TOMATE ROMA (C & D)') but has different receive dates, it needs to group these together. The sorted data should look like the following

    RecDate     ---     ProductDesc                ---       Brand
----------------------------------------------------------------
2017-07-15      ---     TOMATE ROMA              ---      (S & S)
2017-07-16      ---     TOMATE ROMA              ---      (A & B)
2017-07-17      ---     TOMATE ROMA              ---      (C & D)
2017-07-25      ---     TOMATE ROMA              ---      (C & D)
2017-07-26      ---     TOMATE ROMA              ---      (C & D)
2017-07-18      ---     TOMATE ROMA              ---      (AAA)

Any suggesation how i can acomplish this ? NOTE: I am using SQL SERVER, and running sql fill the dataTable, and filling a datagridview with this datatable.

Upvotes: 3

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to sort by the minimum of the date for the brand. You can use window functions in the order by clause to do this:

select RecDate, ProductDesc, ProductBrand
from MyTable i
order by i.ProductDesc,
         min(i.RecDate) over (partition by i.ProductBrand),
         i.ProductBrand,
         i.RecDate;

Upvotes: 5

Related Questions