Returning the first value of a field in a grouped resultset

Question

Post reply

Returning the first value of a field in a grouped resultset

james-821832

Newbie

Points: 8
More actions
June 24, 2009 at 9:17 am

#103773

I am trying to return the first value of a field from a result set that is grouped.
To see a snap shot of the data I am pulling from I ran:
select top 5 CallerNum,sessionId, targetnum
from cdrmain
where (CONVERT(datetime, CAST(LocalDay AS varchar(10)), 101) >= DATEADD(day, - 7, GETDATE()))
and callernum = '233'
order by sessionId
Results:
Callernum sessionId targetnum
233124488162313014744242
233124488162616147645070
233124488169517346773000
233124488170115706893353
233124488170415706893353
I need to be able to do a group by on Callernum(233) but get the first result from the targetnum field (13014744242).
When running the query with the group by on callernum, I use the min aggregate function and it returns the smallest value even though it isn't the first value.
select CallerNum,min(sessionId) sessionId, min(targetnum) targetnum
from cdrmain
where (CONVERT(datetime, CAST(LocalDay AS varchar(10)), 101) >= DATEADD(day, - 7, GETDATE()))
and callernum = '233'
group by callernum
Results:
CallerNum sessionId targetnum
233124488162312404324556
Any ideas?

Viewing 5 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

Try this out.

-- Notice how I built a table and populated it with your test data?

-- this makes it MUCH easier for people to help you out

-- all they have to do is cut and paste, and they can get right to it.

-- just like you're going to do with this code!

-- also, I had to make assumptions about your data types. If you had

-- supplied them, then we wouldn't have to do that.

-- See the link in my signature for how to do this.

declare @tmp table (Callernum int, sessionId bigint, targetnum bigint)

insert into @tmp

SELECT 233, 1244881623, 13014744242 UNION ALL

SELECT 233, 1244881626, 16147645070 UNION ALL

SELECT 233, 1244881695, 17346773000 UNION ALL

SELECT 233, 1244881701, 15706893353 UNION ALL

SELECT 233, 1244881704, 15706893353

-- show what's in this table

select * from @tmp

-- use a common-table expression with the row_number() function.

;with CTE AS

( -- get row numbers for the data, resetting at 1 at each change in Callernum

select Callernum,

sessionId,

targetnum,

ROW_NUMBER() OVER (PARTITION BY Callernum order by CallerNum) AS RowNbr

from @tmp

)

-- get just the rows with RowNbr = 1

select Callernum,

sessionId,

targetnum

from CTE

where RowNbr = 1

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

If you need to get the first value for a given Callernum (like in your example for '233'), you simply could use top 1 clause (based on Wayne's sample data - Thanx for providing! ):

SELECT TOP 1 CallerNum, sessionId, targetnum

FROM @tmp

WHERE

callernum = '233'

ORDER BY sessionId, targetnum

@Wayne: When the request is to get the values for more than one Callernum, I'd go with your solution, but I would change

ROW_NUMBER() OVER (PARTITION BY Callernum order by CallerNum) AS RowNbr

to

ROW_NUMBER() OVER (PARTITION BY Callernum order by CallerNum, sessionId, targetnum) AS RowNbr

to ensure to get the first sessionid and the first targetnum at position 1.

If not, you might end up with a different order if the data weren't inserted in the proper order.

Example:

Check the result of your code with the following insert order:

insert into @tmp

SELECT 233, 1244881695, 17346773000 UNION ALL

SELECT 233, 1244881623, 13014744242 UNION ALL

SELECT 233, 1244881626, 16147645070 UNION ALL

SELECT 233, 1244881701, 15706893353 UNION ALL

SELECT 233, 1244881704, 15706893353

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 3

lmu92 (6/24/2009)
@Wayne: When the request is to get the values for more than one Callernum, I'd go with your solution, but I would change
ROW_NUMBER() OVER (PARTITION BY Callernum order by CallerNum) AS RowNbr
to
ROW_NUMBER() OVER (PARTITION BY Callernum order by CallerNum, sessionId, targetnum) AS RowNbr
to ensure to get the first sessionid and the first targetnum at position 1.
If not, you might end up with a different order if the data weren't inserted in the proper order.

Lutz:

good call. However, I did it this way on purpose. In the original post, the OP stated:

When running the query with the group by on callernum, I use the min aggregate function and it returns the smallest value even though it isn't the first value.

Based on this, I figured that he didn't want the smallest, but instead what was actually first, regardless of the sorting. Ordering by the other fields would have done this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

james-821832 Newbie Points: 8 More actions · Answer 4

Thank you both for your responses. While I was waiting I cobbled together my own solution which was to concatenate the sessionid and callernum grab the min from there and then separate the two in the application.