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 Monday, July 22, 2013 10:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:06 PM
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.
Post #1476340
Posted Tuesday, July 23, 2013 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1476586
Posted Tuesday, July 23, 2013 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:06 PM
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
Post #1476589
Posted Tuesday, July 23, 2013 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1476606
Posted Tuesday, July 23, 2013 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:06 PM
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
Post #1476613
Posted Tuesday, July 23, 2013 9:00 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: Today @ 6:47 AM
Points: 3,912, Visits: 8,859
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.
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 #1476631
Posted Tuesday, July 23, 2013 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1476643
Posted Tuesday, July 23, 2013 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:06 PM
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.
Post #1476649
Posted Tuesday, July 23, 2013 9:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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 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 #1476662
Posted Tuesday, July 23, 2013 9:29 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: Today @ 6:47 AM
Points: 3,912, Visits: 8,859
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.
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 #1476664
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse