Solved

Query table for min date and unique user in SQL SERVER

Posted on 2016-10-07
8
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 51

Assisted Solution

by:Ryan Chong
Ryan Chong earned 83 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 83 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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 83 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 251 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 28

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 13

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 28

Expert Comment

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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