July 22, 2013 at 10:28 pm
SQL Server 2000 Enterprise Edition
create table student
(
rollno int,
name varchar(30),
class varchar(20)
)
insert into student values (1,'Robert','IT')
insert into student values (2,'John','Arts')
insert into student values (2,'Hussain','IT')
insert into student values (1,'David','Science')
insert into student values (3,'Polo','IT')
insert into student values (2,'Jonathan','Science')
insert into student values (4,'Joseph','History')
insert into student values (1,'Richard','History')
insert into student values (1,'Michel','Commerce')
insert into student values (1,'Albert','Geography')
select * from student
rollno name class
1 Robert IT
2 John Arts
2 Hussain IT
1 David Science
3 Polo IT
2 Jonathan Science
4 Joseph History
1 Richard History
1 Michel Commerce
1 Albert Geography
Required Output :
rollno name class
1 Robert IT
2 Hussain IT
3 Polo IT
4 Joseph History
Logic behind required ouput :
Rollno should be unique from all the classes giving priority to :
1.IT
2.Science
3.Arts
4.Commerce
5.Geography
6.Anyone
means, if suppose we are going to fetch a unique roll number (x), and if it is exists in all the classes then first it should be from IT, and if it is not in IT class then it should search in Science Class, if it is not in IT and Science class, then it should search in Arts class, if it is not in IT, Science,Arts then it should search in Commerce Class; like wise.
All existing roll numbers must be unique from all classes giving priority to IT, Science, Arts, Commerce, Geography and if that unique roll number is not from above 5 classes, then it may be of any one class.
Kindly let me know, if I am unclear in my question and/or needs to provide more details. Since I am using SQL Server 2000, so I don't have more functions available in compared to 2005,2008 and higher versions.
July 23, 2013 at 7:56 am
This looks an awful lot like homework. What have you tried so far?
_______________________________________________________________
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/
July 23, 2013 at 7:59 am
Thank you for your reply.
Here is my try till now, but not what I am wants :
CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1),ROLLNO INTEGER,NAME VARCHAR(30),CLASS VARCHAR(30),RNK INTEGER)
SET IDENTITY_INSERT #TempTable OFF
INSERT into #TempTable
select rollno,name,class,
(CASE
WHEN class = 'IT' THEN 1
WHEN class = 'Science' THEN 2
WHEN class = 'Arts' THEN 3
WHEN class = 'Commerce' THEN 4
WHEN class = 'Geography' THEN 5
ELSE 6
END) as rnk
from student
ORDER BY RNK
select z.rollno,min(z.name),min(z.class),min(z.rownumber)
from
(
SELECT ROLLNO, t.name,t.class,t.Rownumber,(t.Rownumber - o.Offset) AS XX,rnk
FROM #TempTable t
INNER JOIN (
SELECT class, MIN(Rownumber) - 1 AS Offset
FROM #TempTable
GROUP BY class
) o ON o.class = t.class
--order by t.rownumber,t.rollno
) z
group by z.rollno
DROP TABLE #TempTable
July 23, 2013 at 8:23 am
OK I like the idea of a temp to hole the order of the class names. I would instead create a table that contains a value to sort by and the value that would determine the order. In other words, no need to make a copy of all the original data just a table that you can use to join to.
something like this:
if OBJECT_ID('tempdb..#Sequence') is not null
drop table #Sequence
create table #Sequence
(
SortOrder int,
ClassName varchar(20)
)
insert #Sequence
select 1, 'IT' union all
select 2, 'Science' union all
select 3, 'Arts' union all
select 4, 'Commerce' union all
select 5, 'Geography' union all
select 6, 'Anyone'
Then you can do something like this.
select s.*, ISNULL(sortorder, 6) as SortOrder
from student s
left join #Sequence se on se.ClassName = s.class
order by rollno, ISNULL(sortorder, 6)
See if that helps nudge you in the right direction.
_______________________________________________________________
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/
July 23, 2013 at 8:31 am
Thanks again for your really helpful reply.
This gives me below output :
rollnonameclassSortOrder
1RobertIT1
1DavidScience2
1MichelCommerce4
1AlbertGeography5
1RichardHistory6
2HussainIT1
2JonathanScience2
2JohnArts3
3PoloIT1
4JosephHistory6
Kindly tell me how do i get required i.e. row number 1,6,9 and 10 please. This grouping is making me crazy.
Thanks and Regards
Girish Sharma
July 23, 2013 at 9:00 am
I didn't read the previous posts. The idea is really similar, but I'm not using a temp table (I probably should). This would be a lot easier on 2005+
SELECT s.*
FROM #student s
JOIN (SELECT 1 priority,'IT' class UNION ALL SELECT
2,'Science' UNION ALL SELECT
3,'Arts' UNION ALL SELECT
4,'Commerce' UNION ALL SELECT
5,'Geography' UNION ALL SELECT
6,class
FROM #student
WHERE class NOT IN( 'IT','Science','Arts','Commerce','Geography')
GROUP BY class) p ON s.class = p.class
JOIN (select rollno, MIN( priority) minpriority
FROM #student s
JOIN (SELECT 1 priority,'IT' class UNION ALL SELECT
2,'Science' UNION ALL SELECT
3,'Arts' UNION ALL SELECT
4,'Commerce' UNION ALL SELECT
5,'Geography' UNION ALL SELECT
6,class
FROM #student WHERE class NOT IN( 'IT','Science','Arts','Commerce','Geography')) p ON s.class = p.class
GROUP BY rollno) o ON s.rollno = o.rollno AND p.priority = o.minpriority
July 23, 2013 at 9:14 am
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.
Here is my solution using the temp table from my previous post. It is pretty much the same as Luis's but I used a temp table instead of doing it on the fly. I agree that in 2005+ this would be a LOT easier.
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 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 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/
July 23, 2013 at 9:16 am
I am really thankful to SSCrazy Eights and You (Ten Centuries). Your query is giving me the correct and required output. Its true that output can be easily get in higher version using Partition by clause, OVER() etc. analytic functions.
Thank again so much.
July 23, 2013 at 9:28 am
gksharmaajmer (7/23/2013)
I am really thankful to SSCrazy Eights and You (Ten Centuries). Your query is giving me the correct and required output. Its true that output can be easily get in higher version using Partition by clause, OVER() etc. analytic functions.Thank again so much.
You are quite welcome. Glad that worked for you and thanks for letting us know. However those are not our names. They are a title or rank based on the number of points. Notice that on your posts your name is "gksharmaajmer" but you rank is "SSC Rookie". 😀
_______________________________________________________________
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/
July 23, 2013 at 9:29 am
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.
July 23, 2013 at 9:38 am
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 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/
July 23, 2013 at 9:40 am
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
July 23, 2013 at 9:50 am
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 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/
July 23, 2013 at 9:51 am
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.
July 23, 2013 at 9:56 am
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. :w00t:
_______________________________________________________________
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply