September 11, 2014 at 10:15 am
Hello all. I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:
create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)
)
insert into #students
(
lastname
,firstname
,address1
)
values ('Hall', 'E','5488 W Catalina'), ('Hall', 'P', '6309 N Olive'),('Hall', 'C', '6309 N Olive'),('Hall','J','7222 N Cocopas')
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas
Hope that makes sense and I hope someone can help. Thanks!
September 11, 2014 at 10:32 am
Like this?
select *
from #students
order by lastname, address1, firstname
Oh nvm...I see what you want. What a strange requirement for an order. One sec...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2014 at 10:54 am
I think you want something like this.
with OrderedResults as
(
select lastname
, firstname
, address1
, ROW_NUMBER() over(partition by address1 order by firstname) as RowNum
from #students
)
, FirstValues as
(
select lastname
, firstname
, address1
, ROW_NUMBER() over(order by firstname) as GroupNumber
from OrderedResults
where RowNum = 1
)
select r.lastname
, r.firstname
, r.address1
from FirstValues fv
left join OrderedResults r on r.address1 = fv.address1
order by GroupNumber
, r.lastname
, r.firstname
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2014 at 10:56 am
BOOM just like that. I tried using a CTE and a row_number, just didn't think of having to do it twice.
Thank you!!
September 11, 2014 at 10:59 am
robert.wiglesworth (9/11/2014)
BOOM just like that. I tried using a CTE and a row_number, just didn't think of having to do it twice.Thank you!!
You are welcome. Glad that worked for you. Make sure you understand what that is doing before you use it. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2014 at 11:01 am
Still reading through it to understand it all. Results look good, now I just want to be able to do it myself next time.
September 11, 2014 at 11:09 am
I think I got it. It looks like I really don't need lastname and firstname in the second CTE. Only need the address to join to and the groupnumber.
thanks again, learned something cool today.
September 11, 2014 at 12:31 pm
robert.wiglesworth (9/11/2014)
I think I got it. It looks like I really don't need lastname and firstname in the second CTE. Only need the address to join to and the groupnumber.thanks again, learned something cool today.
That is true you wouldn't need those columns. See you do understand it. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply