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

Missing Number Find out Expand / Collapse
Author
Message
Posted Friday, December 7, 2012 4:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:49 AM
Points: 61, Visits: 387
Hi

I am doing University Project.In my scenario I have two tables.One table name called studentmaster otherone examapplication. in following structure



studentMaster

RegisterNo Name

101 satheesh
102 xxxx
103 yyyy
104 ZZZZ
105 aaaa
106 bbbb
107 cccc
108 dddd
109 eeee
110 fffff
111 gggg
.
.
.
999 naras


Second table is called Examapplication,In this who are all apply the exam that records stored. like following


Examapplication

Sno Registerno
1 101
2 102
3 105
4 106
5 107
6 120
7 121
8 122




I want following out put(Available details record)

101 - 102,105-107,120-122










Post #1393996
Posted Friday, December 7, 2012 4:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:51 AM
Points: 1,536, Visits: 1,745
Try this

;WITH Missing (missnum, maxid)
AS
(
SELECT
1 AS missnum, (select max(RegisterNo )
FROM
dbo.studentMaster)
UNION ALL
SELECT
missnum + 1, maxid
FROM
Missing
WHERE
missnum < maxid
)
SELECT missnum
FROM
Missing
LEFT OUTER JOIN
dbo.studentMaster tt on tt.RegisterNo = Missing.missnum
WHERE
tt.RegisterNo is NULL
OPTION (MAXRECURSION 0);

Take from
Post #1394000
Posted Friday, December 7, 2012 5:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 2,434, Visits: 7,513
This is the Islands and Gaps problem.

As this is a university project, I won't just give you the answer. However, take a look at this --> http://www.manning.com/nielsen/SampleChapter5.pdf.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1394003
Posted Friday, December 7, 2012 7:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
sql_lock (12/7/2012)
Try this

;WITH Missing (missnum, maxid)
AS
(
SELECT
1 AS missnum, (select max(RegisterNo )
FROM
dbo.studentMaster)
UNION ALL
SELECT
missnum + 1, maxid
FROM
Missing
WHERE
missnum < maxid
)
SELECT missnum
FROM
Missing
LEFT OUTER JOIN
dbo.studentMaster tt on tt.RegisterNo = Missing.missnum
WHERE
tt.RegisterNo is NULL
OPTION (MAXRECURSION 0);

Take from


Oh, be careful now. There are two things wrong with such a thing. The first is that it's a Recursive CTE that counts and is frequently slower than using even a WHILE loop. See the following for more on that subject.
http://www.sqlservercentral.com/articles/T-SQL/74118/

The second thing is that if you have offset ranges with large gaps between the ranges, you could be calculating for a very, very, long time.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394296
Posted Friday, December 7, 2012 11:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:49 AM
Points: 61, Visits: 387
HI Still i am not getting answer.One small changes I use only one table,that table i store a values like a

registerno

101
102
103
104
105
106
107
109
110
120
121
122
123
124
125
127
129
130



I want following result like following manner
101-107,109-110,120-125,127,129-130



Please help me



Post #1394304
Posted Saturday, December 8, 2012 11:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
vs.satheesh (12/7/2012)
HI Still i am not getting answer.One small changes I use only one table,that table i store a values like a

registerno

101
102
103
104
105
106
107
109
110
120
121
122
123
124
125
127
129
130



I want following result like following manner
101-107,109-110,120-125,127,129-130



Please help me


Actually, you got one of the best answers available and you didn't take the time to look or you'd have the problem solved by now. Please see the link that Cadavre gave you. It's one of the best answers possible because it shows you how to do this several different ways and the performance ramifications of each.

Don't forget that this "university project" is for a grade and, I for one, expect you to do a little work on your own to earn your grade because, someday, you're going to be on someone's doorstep askig for a job. If it's for a company that I rely on for some service, then I'd really like its empoyees to know what they're doing.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394351
Posted Monday, December 10, 2012 1:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 2,434, Visits: 7,513
vs.satheesh (12/7/2012)
HI Still i am not getting answer.


Have you had a read through this --> http://www.manning.com/nielsen/SampleChapter5.pdf ? The sample chapter that I linked shows the "guru" method for performing what you want to do, along with lots of explanation.

If you have and are still encountering issues, then post what you have tried so far and I'll happily help. I'm unwilling to do all of the work for you, just as I'd be unwilling to do all of the work for anyone else that posts here but I'm more than happy to help.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1394487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse