Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Group by based upon condition Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
Luis Cazares (7/23/2013)
Sean Lange (7/23/2013)
I didn't just provide a solution because this looks so much like homework and the OP did not answer that one way or the other.


If this post was on a 2008 forum, I would agree. However, I can't believe that they're teaching using SQL Server 2000. Maybe the OP can be clear on that now that he has the answer.


I have seen far worse things being taught.

After the last response I am not as certain this is homework but who knows.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476672
Posted Tuesday, July 23, 2013 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
Hi Sean Lange,

Thanks for correcting me for pronounce the name and I am sorry or my mistake on it. Since, I am an Oracle DBA, and actually this question is related to my friend who is SQL Server DBA. I posted the same question on Oracle Forum too, because I am really very poor in SQL, so first I posted here and then tried to see the solution how it will work in Oracle, so that I can try to see its flow in SQL Server, but again my lack of knowledge failed to achieve the required output.

I am really Thankful to you and Luis Cazares for great replies.

But, I don't know why I am getting below error when I ran your way :
Server: Msg 209, Level 16, State 1, Line 27
Ambiguous column name 'rollno'.

If you and other members allow me to post my friend's question which are only related to version 2000, I will learn SQL and my friend will follow me.

Regards
Girish Sharma
Post #1476674
Posted Tuesday, July 23, 2013 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
gksharmaajmer (7/23/2013)

But, I don't know why I am getting below error when I ran your way :
Server: Msg 209, Level 16, State 1, Line 27
Ambiguous column name 'rollno'.


Try this.

It actually worked fine on my 2008 box but honestly I would expect to see that message too.

select s.*, ISNULL(se.sortorder, 6) as SortOrder
from student s
left join #Sequence se on se.ClassName = s.class
join
(
select rollno, min(ISNULL(sortorder, 6)) as SortOrder
from student s
left join #Sequence se on se.ClassName = s.class
group by rollno
) g on g.rollno = s.rollno and g.SortOrder = ISNULL(se.sortorder, 6)
order by s.rollno, ISNULL(se.sortorder, 6)



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476683
Posted Tuesday, July 23, 2013 9:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 3,637, Visits: 7,932
You'll always be welcome here to learn as we all learn here.
The error you're getting is because the rollno on the order by is not qualified. Just add "s." before rollno and everything should be ok.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476684
Posted Tuesday, July 23, 2013 9:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
Luis Cazares (7/23/2013)

The error you're getting is because the rollno on the order by is not qualified. Just add "s." before rollno and everything should be ok.


Here is an odd one...I changed my compatibility mode to 2k and I got that error. Looking at the code it should throw that in 2008 also because the column truly is ambiguous. In 2k mode it fails but it can be qualified with either g or s and it works (as expected). In 2008 it apparently doesn't care that it is ambiguous.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476689
Posted Tuesday, July 23, 2013 9:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
Yes, now no error, no more doubt on this question... only big thanks from mine and my friend side.

We both learnt good stuff with your and Luis Cazares's great replies.

Regards
Girish Sharma
Post #1476690
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse