Nested Select ?

Question

Nested Select ?

rothj

Hall of Fame

Points: 3412
More actions
September 24, 2010 at 9:04 am

#224308

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.

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

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

rothj Hall of Fame Points: 3412 More actions · Answer 2

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.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

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 🙂

rothj Hall of Fame Points: 3412 More actions · Answer 5

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.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

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?

rothj Hall of Fame Points: 3412 More actions · Answer 7

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