September 24, 2010 at 9:04 am
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:46 6
EDM_SOARC 09/21/2010 10:48 100
EDM_SOARC 09/22/2010 11:00 250
MS4_XIM 09/21/2010 09:46 25
MS4_XIM 09/22/2014 01:01 125
SOARC_SCC 09/21/2010 02:25 300
SOARC_SCC 09/21/2010 01:20 500
SOARC_SCC 09/20/2010 07:07 30
SOARC_SCC 09/22/2010 09:09 900
I want the Top 1 with the most recent DATE.
like:
EDM_SOARC 09/22/2010 11:00 250
MS4_XIM 09/22/2014 01:01 125
SOARC_SCC 09/22/2010 09:09 900
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.
September 24, 2010 at 9:08 am
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..
September 24, 2010 at 9:13 am
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.
September 24, 2010 at 9:22 am
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
September 24, 2010 at 9:25 am
rothj (9/24/2010)
Yeah, I realized it looked like crap and I was just reviewing the Forum Ettiquettes to correct it. SorryI 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 🙂
September 24, 2010 at 9:28 am
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.
September 24, 2010 at 9:46 am
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?
September 24, 2010 at 9:48 am
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