Subquery

  • 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

  • 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/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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

  • 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

  • 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