Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group by based upon condition


Group by based upon condition

Author
Message
gksharmaajmer
gksharmaajmer
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 112
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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 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)
gksharmaajmer
gksharmaajmer
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 112
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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 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)
gksharmaajmer
gksharmaajmer
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 112
Thanks again for your really helpful reply.
This gives me below output :

rollno name class SortOrder
1 Robert IT 1
1 David Science 2
1 Michel Commerce 4
1 Albert Geography 5
1 Richard History 6
2 Hussain IT 1
2 Jonathan Science 2
2 John Arts 3
3 Polo IT 1
4 Joseph History 6



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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18142
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




Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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 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)
gksharmaajmer
gksharmaajmer
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 112
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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". :-D

_______________________________________________________________

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)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18142
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.


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search