get the first row of each group ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get the first row of each group ...

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-11-26 : 13:14:53

Hello guys !

I would like to do the following:
Let's say that I have a table of ... for example addresses. I have a ClientID, and all the fields that are usefull for an address. Now I want to get for each client the address (of course each client might have several addresse) with the biggest postal code. Of course this is nonsens, but just to give an example.
Now, is there any way in SQL to group by certain fields (in this case clientID), order by the Postal code and take only the first row of each group?
The way I would do it now is to group by clientID and then get the max(PostalCode). But this is not very nice, as two addresses of a same client might have the same postal code...

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-26 : 15:38:46

Here is one way. I created a very simple example based on your "fake" requirments.



--create fake table
create table #address
	(addressid int
	,clientid int
	,address1 varchar(25)
	,city varchar(25)
	,postalcode varchar(25))

--enter fake data
insert #address
select	1, 1, '12 main st', 'austin', '12345' union all
select	2, 1, '13 main st', 'austin', '12345' union all
select	3, 2, '5 nanjing', 'taibei', '54321' union all
select	4, 2, '12 nanjing', 'taibei', '65432' 

--get 1st row from group
select	addressid
	,clientid
	,address1
	,city
	,postalcode
from	#address a
where	addressid = (select top 1 addressid 
			from #address 
			where clientid = a.clientid
			order by postalCode desc)

drop table #address

output:
addressid   clientid    address1                  city                      postalcode                
----------- ----------- ------------------------- ------------------------- ------------------------- 
2           1           13 main st                austin                    12345
4           2           12 nanjing                taibei                    65432

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 00:54:39

Also refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

If you use SQL Server 2005, you can make use of row_number() as well

Madhivanan

Failing to plan is Planning to fail

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-11-27 : 02:34:34

Lots of thanks to you both. I did not know that I could reuse a (the table used in the "outer" sql-query) in the "inner" query ! This will solve so many of my problems !
TG, thank you for the step by step example specially made for me !!!

Best regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-11-27 : 06:01:13

Just one comment on that: This solution is rather heavy ;-)

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-27 : 06:07:59

This will be the lightest of the solutions you get I think.

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-11-27 : 06:21:47

I suppose. Perhaps I should suggest this to the International Organization for Standardization ? :-)

my favorit hoster is ASPnix : www.aspnix.com !