Clever Geek Handbook

Sql query highlighting is ok

I have a set of sorted records (Order BY) ProductDesc, RecDate, then ProductBrand and this gives me the following 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 (eg "TOMATE ROMA (C and D)") but have different dates reception, you need to combine them together. The sorted data should look like this:

    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 suggestion how can I internalize this? NOTE. I am using SQL SERVER and executing sql filling dataTable and filling datagridview with this datatable.

+3


source to share


1 answer


You want to sort by minimum date for the brand. You can use the window functions in the sentence order by

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;
+5


source






More articles:


All Articles