locked
Subquery RRS feed

  • Question

  • 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
    Tuesday, August 9, 2011 4:15 PM

Answers

  • Here's the answer I got in another forum:

    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
    

    Seems to work


    Jim
    • Marked as answer by JimS-Indy Thursday, August 11, 2011 1:56 PM
    Thursday, August 11, 2011 1:55 PM

All replies

  • From the query you provide above you have Max(W.ID), however did you mean to put Max(W.LaborRateID)? given ID is an identity column.

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Wednesday, August 10, 2011 1:29 AM
  • "(Using Highest ID as a proxy for most recent...)"
    Jim
    Wednesday, August 10, 2011 1:07 PM
  • I think your query is a valid TSQL query. Couldn't it be exuecuted successfully in SQL Server?
    Hope it helps.
    Thursday, August 11, 2011 6:11 AM
  • Hi,

    Could you please post the schema of table 'tblStaffAugTrans' and share some sample data?


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, August 11, 2011 8:19 AM
  • 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]
    


    Jim
    Thursday, August 11, 2011 1:00 PM
  • The query I posted does execute, but it gets me only the first stage of the answer. I need a result set that contains:

    WorkerID, LaborRateID Where LaborRateID is the LaborRateID from the row of that WorkerID's most recent entry (using highest ID as a proxy for most recent...)

    The query I posted has a result set that looks like

    WorkerID, ID Where ID is the identity from the most recent row containing that WorkerID.

    In Access, I'd just use two queries and join them. T-SQL shouldn't have that requirement.


    Jim
    Thursday, August 11, 2011 1:04 PM
  • Here's the answer I got in another forum:

    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
    

    Seems to work


    Jim
    • Marked as answer by JimS-Indy Thursday, August 11, 2011 1:56 PM
    Thursday, August 11, 2011 1:55 PM
  • It would have been much easier to answer your question if you would have provided these two Access queries that are already working.  BTW, in most cases, you should be able to do exactly the same in T-SQL.  Excerpt when you use VBA functions like IIF(); what you can do in Access, you can usually do the same in T-SQL.

    Saturday, August 13, 2011 11:19 AM
  • The query I was using in Access was a poorly-written monster I didn't want to share. The reason I was reworking it is that it took 20 seconds to execute in order to build a default value for a combo box. Fortunately, it was typically executed only once per user session. I was going to have to replace it with two access queries, cascaded. That was simple enough, but I knew that with T-SQL, I could do it with one query (even if it was a nested query), and I want to learn. I'd never seen the Row_Number() function, so I did learn something.

    The query the other forum provided runs sub-second, even when called from Access. From SSMA, it runs almost instantly (every field refrenced is indexed....) Quite a change from my awkward solution in Access.

    I appreciate your help, Sylvain et al. I have learned much from all of you.


    Jim
    Saturday, August 13, 2011 1:49 PM