Question:- Suppose we have a table says tbl_students whose structure is given below:-
Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))
Suppose it contains the following data:-
Studentid Studentname
1 Vivek Johari
2 Chandra Singh
4 Avinash Dubey
6 Akhil
7 Sunny Rana
10 Saurabh
As one can see from the data that some rows are deleted from the table as some studentids are missing.
So write a query which gives the studentid which are deleted from this table
Answer:-
Thanks all for answering that question. I really appreciate the effort of all the people who tried and gives the response.We get multiple answers for this question in which correct answer is given below:-
Correct answer in case the first coulmn is an identity column
Answer given by Arjan Dingemans
WITH Numbers AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < IDENT_CURRENT(‘tbl_students’)
)
SELECT Number
FROM Numbers N
LEFT JOIN tbl_students S ON N.Number = S.StudentId
WHERE S.StudentId IS NULL
Answer given By Chandra Prakash Singh
select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type=’P’
and s.number <= IDENT_CURRENT(‘tbl_students’)
and tbl_students.studentid is null and number > 0
Correct answer in case the first coulmn is not an identity column
Answer given By Avinash Dubey
WITH DeletedRows (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(Studentid) from tbl_Students) UNION ALL SELECT missnum + 1, maxid FROM DeletedRows
WHERE missnum < maxid
)
SELECT missnum FROM DeletedRows
LEFT OUTER JOIN tbl_Students e on e.Studentid = DeletedRows.missnum
WHERE e.Studentid is NULL OPTION (MAXRECURSION 0)
Answer given by Aviral Lamba
select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type=’P’
and s.number < (select MAX(studentid) from tbl_students)
and tbl_students.studentid is null and number > 0
Also I appreciate the effort done by chetan Deshpande in attempting the question
Is this correct??
BEGIN TRAN
DECLARE @MAX INT= (SELECT MAX(Studentid) FROM TBL_STUDENTS)
CREATE TABLE #T1(ID INT)
WHILE(@MAX>0) BEGIN
INSERT INTO #T1 VALUES(@MAX)
SET @MAX=@MAX-1
END
SELECT * FROM #T1 except (SELECT Studentid FROM TBL_STUDENTS)
DROP TABLE #T1
COMMIT TRAN
Pingback: vw Repair Greensboro
Pingback: xembongdatructuyen
Pingback: w88
Pingback: https://www.wolfpackcryptos.io
Pingback: http://amsico.pl/puzzle-dwustronne-maxi-ksiezniczki-108-2316.php
Pingback: https://internetnews.net.pl/forum/nowe-technologie-f7/poznam-opinie-o-funkymedia-t62.html
Pingback: nha cai fun88
Pingback: диодна лазерна епилация
Pingback: playerqq
Pingback: The Majestic Garden
Pingback: Cheap hotels in rome
Pingback: marketing analytics skills
Pingback: i99 casino
Pingback: m88sb
Pingback: idrpokerq
Pingback: http://antykorozja-poznan.pl/
Pingback: fun88.com
Pingback: qiuqiu 99
Pingback: vào m88
Pingback: hondaqq
Pingback: main qq
Pingback: login sbobet888
Pingback: grade miners
Pingback: replica of all iced out jacob and co watch clone iwc paypal
Pingback: social site
Pingback: mejaqq