August 9, 2011 at 8:20 am
I'm revising an Access form to work with SS2k5 backend when I discover a query I should just convert to T-SQL, only I'm having a mental block on it.
I have a table of transactions (tblStaffAugTrans, 700,000 rows). Each row has ID(Identity), WorkerID(Int, FK), LaborRateID(Int, FK). I need to find the most recent (Using Highest ID as a proxy for most recent...) LaborRateID for a given WorkerID. Each worker has many rows, with (perhaps) many LaborRateIDs.
In the end, I need a set of rows containing one entry for each WorkerID:
WorkerID, LaborRateID
This gets me the WorkerID and his/her Highest ID:
SELECT W.WorkerID, Max(W.ID) as HighestID FROM tblStaffAugTrans AS W GROUP BY W.WorkerID
I know this is a simple subquery, but I can't seem to get it done. Anyone wanna jump in?
Jim
August 9, 2011 at 1:46 pm
You will lots of people willing and able to help. However, you have to help us help you. We don't have any idea what your tables or data are like. If you can post some ddl, sample data and desired output based on your sample it will be a great first step in getting tested and working code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2011 at 2:02 pm
row number is very handy for getting data like you are asking...this is a wild guess to get you started, it might or might not be right, but is a gentle shove in the right direction.
SELECT *
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY W.WorkerID ORDER BY W.ID DESC ) AS RW ,
W.WorkerID,
W.ID
FROM tblStaffAugTrans AS W
) MySubQuery
WHERE RW = 1
Lowell
August 9, 2011 at 2:03 pm
SELECT * FROM
(
SELECT id, WorkerID, LaborRateID ROW_NUMBER() OVER(PARTITION BY WorkerID ORDER BY id DESC) AS rn FROM ...
) dta
WHERE rn = 1
There might be a way to do it more efficiently than that but I don't have time to do the research atm.
August 9, 2011 at 4:10 pm
Only one table in use:
USE [CPASD]
GO
/****** Object: Table [dbo].[tblStaffAugTrans] Script Date: 08/09/2011 18:08:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStaffAugTrans](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VendorID] [int] NOT NULL,
[WorkerID] [int] NOT NULL DEFAULT ((0)),
[WorkDate] [datetime] NOT NULL,
[LaborRateID] [int] NOT NULL,
[StdHours] [float] NOT NULL DEFAULT ((0)),
[OTHours] [float] NOT NULL DEFAULT ((0)),
[DTHours] [float] NOT NULL DEFAULT ((0)),
[WorkOrderID] [int] NOT NULL,
[DisciplineID] [int] NOT NULL DEFAULT ((0)),
[PurchaseOrderID] [int] NOT NULL DEFAULT ((0)),
[Comment] [nvarchar](150) NULL,
[BatchID] [int] NULL,
[TransactionStatus] [nvarchar](50) NULL,
[MicronWkNbr] [int] NULL DEFAULT ((0)),
[UserID] [nvarchar](50) NOT NULL,
[UpdateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tblStaffAugTrans$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[ID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[VendorID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'VendorID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[WorkerID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'WorkerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[WorkDate]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'WorkDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[LaborRateID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'LaborRateID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[StdHours]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'StdHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[OTHours]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'OTHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[DTHours]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'DTHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[WorkOrderID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'WorkOrderID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[DisciplineID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'DisciplineID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[PurchaseOrderID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'PurchaseOrderID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[Comment]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'Comment'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[BatchID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'BatchID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[TransactionStatus]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'TransactionStatus'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[MicronWkNbr]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'MicronWkNbr'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[UserID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'UserID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[UpdateDateTime]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'COLUMN',@level2name=N'UpdateDateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'SQLCPAS110728.[tblStaffAugTrans].[PrimaryKey]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblStaffAugTrans', @level2type=N'CONSTRAINT',@level2name=N'tblStaffAugTrans$PrimaryKey'
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblBatchtblStaffAugTrans] FOREIGN KEY([BatchID])
REFERENCES [dbo].[tblBatch] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblBatchtblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblDisciplinetblStaffAugTrans] FOREIGN KEY([DisciplineID])
REFERENCES [dbo].[tblDiscipline] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblDisciplinetblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblPOtblStaffAugTrans] FOREIGN KEY([PurchaseOrderID])
REFERENCES [dbo].[tblPO] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblPOtblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblStaffAugLaborRatestblStaffAugTrans] FOREIGN KEY([LaborRateID])
REFERENCES [dbo].[tblStaffAugLaborRates] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblStaffAugLaborRatestblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblTransTypetblStaffAugTrans] FOREIGN KEY([TransactionStatus])
REFERENCES [dbo].[tblTransType] ([TransType])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblTransTypetblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblUserstblStaffAugTrans] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUsers] ([UserID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblUserstblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblVendortblStaffAugTrans] FOREIGN KEY([VendorID])
REFERENCES [dbo].[tblVendor] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblVendortblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblWorkerstblStaffAugTrans] FOREIGN KEY([WorkerID])
REFERENCES [dbo].[tblWorkers] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblWorkerstblStaffAugTrans]
GO
ALTER TABLE [dbo].[tblStaffAugTrans] WITH NOCHECK ADD CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblWorkOrderstblStaffAugTrans] FOREIGN KEY([WorkOrderID])
REFERENCES [dbo].[tblWorkOrders] ([ID])
GO
ALTER TABLE [dbo].[tblStaffAugTrans] CHECK CONSTRAINT [tblStaffAugTrans$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblWorkOrderstblStaffAugTrans]
Jim
August 11, 2011 at 7:55 am
I tested Lowell's contribution, thank you, Lowell and Ninja. I altered it slightly, here's the final query:
SELECT *
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY W.WorkerID ORDER BY W.ID DESC ) AS RW ,
W.WorkerID,
W.LaborRateID
FROM tblStaffAugTrans AS W
) MySubQuery
WHERE RW = 1
Note the only change is to extract LaborRateID instead of ID.
I tried to look up "PARTITION BY" in my cheap little "SQL in a Nutshell" desktop reference, but found nothing. So, I"ll have to read up on it elsewhere. Best source?
Thanks again, folks!
Jim
August 11, 2011 at 8:05 am
Best source of any documentation is BOL. The partition will be found as part of the syntax for ROW_NUMBER.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply