Nested Select ?

  • I have a table with some transaction counts.

    I want to get a list of the most recent transaction count per Name.

    Data looks like this:

    INAME DATE TRX

    EDM_SOARC           09/21/2010 10:466

    EDM_SOARC           09/21/2010 10:48100

    EDM_SOARC           09/22/2010 11:00250

    MS4_XIM              09/21/2010 09:46 25

    MS4_XIM              09/22/2014 01:01 125

    SOARC_SCC           09/21/2010 02:25300

    SOARC_SCC           09/21/2010 01:20500

    SOARC_SCC           09/20/2010 07:0730

    SOARC_SCC           09/22/2010 09:09900

    I want the Top 1 with the most recent DATE.

    like:

    EDM_SOARC 09/22/2010 11:00250

    MS4_XIM 09/22/2014 01:01 125

    SOARC_SCC 09/22/2010 09:09900

    I think I need a TOP 1 Select within a Select but I am not sure how to do this. Thanks

    The DATE field is DateTime datatype.

  • 693 visits, 246 points and still you are not clear on how to ask a question.. Hmm.. without ready-to-use sample data, its hard to spend time on checking this.. lets wait for other volunteers to attend this..

  • Yeah, I realized it looked like crap and I was just reviewing the Forum Ettiquettes to correct it. Sorry

    I thought it would be a quick an easy select for the experienced code writers.

  • Ok, with my frustration apart, here is one of the solution for this:

    First lest prepare a temp table and sample data:

    DECLARE @tab TABLE

    (

    INAME VARCHAR(20),

    [DATE] DATETIME,

    TRX INT

    )

    INSERT INTO @tab (INAME , [DATE], TRX)

    SELECT 'EDM_SOARC','09/21/2010 10:46', 6

    UNION ALL SELECT 'EDM_SOARC','09/21/2010 10:48', 100

    UNION ALL SELECT 'EDM_SOARC','09/22/2010 11:00', 250

    UNION ALL SELECT 'MS4_XIM ','09/21/2010 09:46',25

    UNION ALL SELECT 'MS4_XIM ','09/22/2014 01:01', 125

    UNION ALL SELECT 'SOARC_SCC','09/21/2010 02:25', 300

    UNION ALL SELECT 'SOARC_SCC','09/21/2010 01:20', 500

    UNION ALL SELECT 'SOARC_SCC','09/20/2010 07:07', 30

    UNION ALL SELECT 'SOARC_SCC','09/22/2010 09:09', 900

    Now the query to find the max(date) for each INAME

    ; WITH CTE0 AS

    (

    SELECT

    RID = ROW_NUMBER() OVER (PARTITION BY INAME ORDER BY [DATE] DESC)

    ,INAME

    ,[DATE]

    , TRX

    FROM @tab

    )

    SELECT Tab.INAME , Tab.[DATE], Tab.TRX

    FROM CTE0 Tab

    WHERE RID = 1

  • rothj (9/24/2010)


    Yeah, I realized it looked like crap and I was just reviewing the Forum Ettiquettes to correct it. Sorry

    I thought it would be a quick an easy select for the experienced code writers.

    No issues, this interest , to feeling u had , in itself is awesome.. any experienced code writer will surely love to run his/her code against a given set of input values, test his/her code, optimize it and they provide it on a online forum.. they will surely not want to provide below-par codes.. so it upto you how u extract the best from them..Hmm.. just have a glance over how i prepared the sample data.. HELP US HELP YOU.. as simple as that, mate 🙂

  • Understood. You are absolutely correct and I will definitely apply this lesson learned in the future.

    Thank you very much for assisting me despite my short sightedness.

  • rothj (9/24/2010)


    Understood. You are absolutely correct and I will definitely apply this lesson learned in the future.

    Thank you very much for assisting me despite my short sightedness.

    Thanks for understanding, Roth.. btw,did my code help you?

  • Yes, it gave me just what I was looking for. Thank you!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply