?
Solved

Query table for min date and unique user in SQL SERVER

Posted on 2016-10-07
8
Medium Priority
?
123 Views
Last Modified: 2016-11-13
Here is my table

Job | Price | Changed By | Date

1 |100 | Bill  | 2016/08/01
1 |100 | Bob| 2016/08/02
1 |100 | Susan| 2016/08/03

2 |222 | Bill | 2016/08/01
2 |222 | Bob| 2016/08/02
2 |222 | Bill | 2016/08/03
2 |222 | Bob| 2016/08/04

3 |176 | Susan| 2016/08/01
3 |176 | Bill | 2016/08/02
3 |176 | Susan| 2016/08/03

What I want is the job, price and changed by for record with the minimum date. The results I would like to see is:

1 |100 | Bill  | 2016/08/01
2 |222 | Bill | 2016/08/01
3 |176 | Susan| 2016/08/01

Thanks in advance for your help
0
Comment
Question by:princeservice
8 Comments
 
LVL 54

Assisted Solution

by:Ryan Chong
Ryan Chong earned 332 total points
ID: 41833949
quick try:

select job, price , [changed by], date
from (
select job, price , [changed by], date, row_number() over (partition by job order by date) idx
from yourTable
) a
where idx = 1

Open in new window

1
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 332 total points
ID: 41834001
Or

SELECT Job, Price, [Changed By], Date
FROM yourTable
INNER JOIN (
SELECT Job, Min([Date]) as MinDate
FROM yourTable
GROUP BY Job
) as JobEarliest
ON yourTable.Job = JobEarliest.Job AND yourTable.[Date] = JobEarliest.MinDate
0
 
LVL 38

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 332 total points
ID: 41834035
Best suited .. my fav , Cross Apply..

SELECT k.Job , b.* FROM 
(
	SELECT DISTINCT Job FROM TableName
)k
CROSS APPLY
(
	SELECT TOP 1 Price , ChangedBy, Date FROM TableName m
	WHERE k.job = m.job
	ORDER BY m.Date ASC
)b

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 1004 total points
ID: 41834992
The CROSS APPLY will need a minimum of two scans through the table, resulting in more I/O overhead. Using the test data below, I got 4 logical reads instead of 1 since the "applied query" was executed 4 times. This is because of the way CROSS APPLY works i.e. the "applied" query is run again and again (like in a loop) for each value in the outer table.

A set based operation using windowing functions may be better from a performance perspective (that would be something the author would need to test for).

DECLARE @jobData TABLE ([Job] INT, [Price] DECIMAL(19,4), [ChangedBy] VARCHAR(50), [Date] DATE)

INSERT INTO @jobData ([Job], [Price], [ChangedBy], [Date])
VALUES (1 ,100 , 'Bill'  , '2016/08/01'),
       (1 ,100 , 'Bob'   , '2016/08/02'),
       (1 ,100 , 'Susan' , '2016/08/03'),
       (2 ,222 , 'Bill'  , '2016/08/01'),
       (2 ,222 , 'Bob'   , '2016/08/02'),
       (2 ,222 , 'Bill'  , '2016/08/03'),
       (2 ,222 , 'Bob'   , '2016/08/04'),
       (3 ,176 , 'Susan' , '2016/08/01'),
       (3 ,176 , 'Bill'  , '2016/08/02'),
       (3 ,176 , 'Susan' , '2016/08/03');

SELECT RankedRows.[Job], 
       RankedRows.[Price], 
       RankedRows.[ChangedBy], 
       RankedRows.[Date], 
       RankedRows.[RowRank]
FROM (SELECT jd.[Job],
             jd.[Price],
             jd.[ChangedBy],
             jd.[Date],
             RANK() OVER (ORDER BY jd.[Date] ASC) AS RowRank
      FROM @jobData AS jd
     ) AS RankedRows ([Job], [Price], [ChangedBy], [Date], [RowRank])
WHERE RankedRows.RowRank = 1
ORDER BY RankedRows.[Job], 
         RankedRows.[Price],
         RankedRows.[ChangedBy];

Open in new window

0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41835019
Performance is not a considered as of now. Will see to if required. Also this kind of solution is already given by Ryan. Also note that TOP 1 , Cross Apply, Small Order By performances better.  This is because small sorts scales really well. Try it out with a Million Record.  E.g. Million Records - Transaction table ..
For each product , get last transaction date with product information.
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41835091
Sorry - didn't see the solution by Ryan. It is similar.

As for whether CROSS APPLY is better, it's a case of "it depends". The trade off is between I/O and CPU. So, depending upon the resource available and critical, the decision can be made.

In a trivial query where no major mathematical operations are being done, the read counts in case of CROSS APPLY will be higher, but CPU time (as captured by STATISTICS TIME and STATISTICS IO) will be lower (due to the smaller data set).

For example, in the test below (run on a basic Core2Duo with 2GB RAM, MAXDOP = 1) the reads required in the windowing function based approach are less, but it needs more CPU. The CROSS APPLY needs more reads, but less CPU.

USE tempdb;
GO
/*
Number of rows: 1,213,170
-----------------------------------------------------
Results (Compiled). Detailed results towards the end
-----------------------------------------------------
Windowing Function: Table 'HugeSalesOrder'. Scan count 1, logical reads 5613, physical reads 2, read-ahead reads 5600, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CROSS APPLY method: Table 'HugeSalesOrder'. Scan count 121318, logical reads 370118, physical reads 2, read-ahead reads 5600, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Windowing Function: Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CROSS APPLY method: Table 'Product'. Scan count 0, logical reads 242634, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Windowing Function: CPU time = 1171 ms,  elapsed time = 12644 ms.
CROSS APPLY method: CPU time = 1109 ms,  elapsed time = 11008 ms.

Conclusion:
Windowing Function: More CPU, less reads & scans
CROSS APPLY method: Less CPU, more reads & scans
*/
SET NOCOUNT ON;
--Safety Check
IF OBJECT_ID('dbo.HugeSalesOrder','U') IS NOT NULL
    DROP TABLE dbo.HugeSalesOrder;
GO

CREATE TABLE dbo.HugeSalesOrder (SalesOrderId          INT          NOT NULL, 
                                 SalesOrderDetailId    INT          NOT NULL,
                                 ProductId             INT          NOT NULL,
                                 TransactionDate       DATETIME     NOT NULL,
                                 CarrierTrackingNumber VARCHAR(255)     NULL,
                                 CONSTRAINT pk_HugeSalesOrder PRIMARY KEY CLUSTERED (SalesOrderId, 
                                                                                     SalesOrderDetailId, 
                                                                                     ProductId, 
                                                                                     TransactionDate
                                                                                    )
                                );
GO

INSERT INTO dbo.HugeSalesOrder (SalesOrderId, SalesOrderDetailId, ProductId, TransactionDate, CarrierTrackingNumber)
SELECT sod.SalesOrderID,
       sod.SalesOrderDetailID,
       sod.ProductID,
       DATEADD(DAY, so.[increment], sod.ModifiedDate) AS TransactionDate,
       sod.CarrierTrackingNumber
FROM AdventureWorks2014.Sales.SalesOrderDetail AS sod
CROSS JOIN (VALUES (1),
                   (2),
                   (3),
                   (4),
                   (5),
                   (6),
                   (7),
                   (8),
                   (9),
                   (10)
           ) AS so(increment);
GO

--Check # of rows available
SELECT COUNT(*) FROM dbo.HugeSalesOrder;
GO

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO
PRINT '*********************************************************'
PRINT 'Query 1 Execution. Statistics output after this point.';
PRINT '*********************************************************'
GO
SELECT 'Query 1 Start' AS Operation, GETDATE() AS LogTime;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
--Rank-based solution
SELECT RankedRows.SalesOrderId, 
       RankedRows.SalesOrderDetailId, 
       RankedRows.ProductId, 
       RankedRows.TransactionDate, 
       RankedRows.CarrierTrackingNumber,
       advProduct.Name AS ProductName,
       advProduct.ProductLine,
       advProduct.ProductNumber
FROM (SELECT jd.[SalesOrderId],
             jd.[SalesOrderDetailId],
             jd.[ProductId],
             jd.[TransactionDate],
             jd.[CarrierTrackingNumber],
             RANK() OVER (PARTITION BY jd.[SalesOrderId],
                                       jd.[SalesOrderDetailId],
                                       jd.[ProductId] 
                          ORDER BY jd.[TransactionDate] ASC) AS RowRank
      FROM dbo.HugeSalesOrder AS jd
     ) AS RankedRows (SalesOrderId, SalesOrderDetailId, ProductId, TransactionDate, CarrierTrackingNumber, RowRank)
INNER JOIN AdventureWorks2014.Production.Product AS advProduct ON RankedRows.ProductId = advProduct.ProductID
WHERE RankedRows.RowRank = 1;
GO
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
SELECT 'Query 1 End' AS Operation, GETDATE() AS LogTime;

PRINT '*********************************************************'
PRINT 'Query 1 Execution Completed. Cleaning buffers and procedure cache now.';
PRINT '*********************************************************'
GO

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

PRINT '*********************************************************'
PRINT 'Query 2 Execution. Statistics output after this point.';
PRINT '*********************************************************'
GO

SELECT 'Query 2 Start' AS Operation, GETDATE() AS LogTime;
--CROSS-APPLY SOLUTION
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT b.SalesOrderId, 
       b.SalesOrderDetailId, 
       b.ProductId, 
       b.TransactionDate, 
       b.CarrierTrackingNumber,
       b.ProductName, 
       b.ProductLine, 
       b.ProductNumber
FROM 
(
    SELECT DISTINCT hso.SalesOrderId,
                    hso.SalesOrderDetailId,
                    hso.ProductId
    FROM dbo.HugeSalesOrder AS hso
)k
CROSS APPLY
(
    SELECT TOP 1 bhso.SalesOrderId,
                 bhso.SalesOrderDetailId,
                 bhso.ProductId,
                 bhso.TransactionDate,
                 bhso.CarrierTrackingNumber,
                 advProduct.Name AS ProductName,
                 advProduct.ProductLine AS ProductLine,
                 advProduct.ProductNumber AS ProductNumber
    FROM dbo.HugeSalesOrder AS bhso
    INNER JOIN AdventureWorks2014.Production.Product AS advProduct ON bhso.ProductId = advProduct.ProductID
    WHERE bhso.SalesOrderId = k.SalesOrderId
      AND bhso.SalesOrderDetailId = k.SalesOrderDetailId
      AND bhso.ProductId = k.ProductId
    ORDER BY bhso.TransactionDate ASC
)b
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
SELECT 'Query 2 End' AS Operation, GETDATE() AS LogTime;
PRINT '*********************************************************'
PRINT 'Query 2 Execution Completed.';
PRINT '*********************************************************'
GO


/* 
-------------
Results (Raw)
-------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*********************************************************
Query 1 Execution. Statistics output after this point.
*********************************************************

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'HugeSalesOrder'. Scan count 1, logical reads 5613, physical reads 2, read-ahead reads 5600, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1171 ms,  elapsed time = 12644 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
*********************************************************
Query 1 Execution Completed. Cleaning buffers and procedure cache now.
*********************************************************
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*********************************************************
Query 2 Execution. Statistics output after this point.
*********************************************************

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'HugeSalesOrder'. Scan count 121318, logical reads 370118, physical reads 2, read-ahead reads 5600, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 242634, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1109 ms,  elapsed time = 11008 ms.
*********************************************************
Query 2 Execution Completed.
*********************************************************
*/

Open in new window

1
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41835094
Excellent explanation bro ! Great.
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41885784
Dear Princeservice,
Can you explain us why you have selected  <<https://www.experts-exchange.com/questions/28975046/Query-table-for-min-date-and-unique-user-in-SQL-SERVER.html#a41834992>> as accepted answer.

Ryan has given the same answer and was first one to answer.

Thanks in advance !

Regards,
Pawan
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question