Query table for min date and unique user in SQL SERVER

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
Robert FrancisDirector of Continuous ImprovementAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
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
Dale FyeCommented:
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
Pawan KumarDatabase ExpertCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
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
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
Pawan KumarDatabase ExpertCommented:
Excellent explanation bro ! Great.
0
Pawan KumarDatabase ExpertCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.