Random Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Random Query

Author

Topic

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-06-08 : 11:02:30

im tryin to get the numbers from 1 to 7 that have not been added to the table already

this is my Simple query
select Day from foodmapping
Group by Day
which returns
4
5

so i need
1
2
3
6
7

returned from this query
any ideas wud be appreciated
thanks in advance

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-08 : 19:10:05

One way of doing it (in a stored proc)


DECLARE @days TABLE (DayNum int)

INSERT INTO @days(dayNum)
   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7

SELECT D.DayNum 
FROM  foodmapping F RIGHT JOIN @days D ON D.DayNum = F.Day
WHERE F.Day IS NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-09 : 04:00:49

http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-11 : 05:57:36

Try out this..

you can find function F_TABLE_NUMBER_RANGE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE


Select a.Number From F_TABLE_NUMBER_RANGE(1,7) As a Left outer Join 
(select Day from foodmapping Group by Day ) As b On 
A.Number = b.Day
Where b.Day is null

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources