SQL Query Problem...

[man12_patil3]

I have table with 3 Columns id(Primary), name, percent  , i want to know the missing id from Column id and repeated percent with No. of count .

 

For e.g. id(primary)    =  1,  4,  5,  7,  8, 12, 13, 14, 15, 16  (missing id is 2,3,6,9,10,11)

                  Percent      = 88, 75, 88, 65, 55, 88, 88, 64, 65, 70 (repeated percent is 88(4), 65(2) )

 

i want to Sql Query for above output.

[kickstart]

Hi

 

Here you go.

 

This code will find you all the unused Ids. It relies on you having  a table to generate integers (called integers with a single integer column called i with 10 rows for values of i from 0 to 9). Currently copes with ids up to 9999 but can easily be extended to cope with higher ids.

 

SELECT AllPossId.PossId
FROM (SELECT a.i+b.i*10+c.i*100+d.i*1000 As PossId from integers a, integers b, integers c, integers d) AllPossId
LEFT OUTER JOIN SomeTable ON AllPossId.PossId = SomeTable.Id
JOIN (SELECT MAX(id) AS MaxId FROM SomeTable) TheMaxId
WHERE SomeTable.Id IS NULL
AND AllPossId.PossId <= TheMaxId.MaxId

 

This will find you all the duplicate percentages and how many times they are there.

 

SELECT Percent, COUNT(*) AS PercentCount
FROM SomeTable
GROUP BY Percent
HAVING PercentCount >= 2

 

All the best

 

Keith