CODEWITHSUNDEEP
sqlsql-server
PJLG
PJLG

Reputation: 105

Multiple aggregate functions

In a SQL Server table with Item, Supplier, Value and Date columns where are records of all item purchases, I need records, by item, with minimum value of the last 180 days only.

A sample table with only one Item: 

Item   | Supplier | Value | Date          
---------------------------------------
123    |   28     |  115  | 2013-09-25 
123    |   25     |  125  | 2013-11-30 
123    |   30     |  120  | 2014-01-15 
123    |   25     |  130  | 2014-04-30

The query result should be: 

123    |   30     |  120  | 2014-01-15

It is possible have records that seems to be duplicated because it can be more than one origin document from the same supplier and date with same Item and value. Only need one of this records.

How can I get it?

Upvotes: 0

Views: 58

Answers (3)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36621

select item, supplier, min_value
from
(
select item, supplier, min_value, row_number() over(order by min_value) as row_num
from
(
select item, supplier, min(value) as min_value
from sales
where DATEDIFF(DAY, mydate ,GETDATE()) <= 180
group by item, supplier
) dt
) dt1
where dt1.row_num = 1

Upvotes: 0

Fabian Bigler
Fabian Bigler

Reputation: 10895

Another simple way of achieving this:

SELECT TOP 1 * FROM TableName
WHERE DATEDIFF(day, Date, GetDate()) <= 180
ORDER BY VALUE

Demo

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

Use a ranking function like Row_Number:

WITH CTE AS
(
  SELECT Item, Supplier,Value, Date,
         RN = ROW_NUMBER() OVER (PARTITION BY Item
                                 ORDER BY Supplier DESC)
  FROM dbo.TableName
  WHERE DATEDIFF(day, Date, GetDate()) <= 180
)
SELECT Item,  Supplier,Value, Date
FROM CTE
WHERE RN = 1

Demo

However, it's not that clear what you want of the duplicates or what a duplicate is at all. I have presumed that the Item determines a duplicate and that the maximum Supplier is taken from each duplicate group.

If you want to use all columns to determine a duplicate your desired result would contain multiple rows because there is no duplicate at all.

Upvotes: 2

Related Questions