Solved

Query table for min date and unique user in SQL SERVER

Posted on 2016-10-07
8
78 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 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 83 total points
Comment Utility
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 83 total points
Comment Utility
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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 83 total points
Comment Utility
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
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 251 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Excellent explanation bro ! Great.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now