sql question

[habesha]

I am a bit stuggering to do this
here is my problem

I have table lets say tbl it has two columns with produciId numeric and version varcahr(40)
here is the table

tbl

productId version

1 0.0
1 1.0
1 2.0
2 2.0
3 0.0
3 1.0


I want to query out records from this table that don't have a previous version for each version.
i.e for example productid 1 has max version of 5.0 therefore it has to have versions from 0.0 to 5.0
but version 2 has max version 2.0 but doesn't have previous versions 1.0 and 0.0 there for I want to get those records with this kind, which don't have a previous version for each of there version

Thanks

 

[SQLDenis]

create table #test (productId  int , version decimal(5,2))


insert #test

select 1 ,              0.0 union all
select 1  ,             1.0 union all
select 1   ,            2.0 union all
select 2    ,           2.0 union all
select 3     ,          0.0 union all
select 4      ,         0.0 union all
select 4     ,          1.0 union all
select 4     ,          2.0 union all
select 4      ,         4.0

Assuming versions are always whole numbers

select productId
from #test
group by productId
having count(*) <> max(convert(int,version))+ 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Or...

Select t1.ProductID, t1.Version
from MyTable t1
join (Select ProductID, Version
      from MyTable
      Group By ProductID, Version
      Having Count(ProductID) < 2) t2
on t1.ProductID = t2.ProductID

You'll have to test this to make sure it works, but it should.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[rac2]

A lot depends on whether you know all of the possible version numbers for all of the products. What about version 10.4? What is the previous version? It could be 10.39, 10.3, 10.0, or 9.0. And the fact that [version] is VARCHAR suggests that versions can be non-numeric.

If you know what versions are possible, then you could put them in a table, call it PossibleProductVersions with columns product_id and version.

A query like this will yield the versions you have for each product.

SELECT DISTINCT productId, version
FROM MyTable

That can be JOINed with the new table to obtain the missing versions.

SELECT PPV.productId, PPV.version
FROM PossibleProductVersions PPV
LEFT JOIN (
      SELECT DISTINCT productId, version
      FROM MyTable
      ) A ON A.productId = PPV.productId
         AND A.version = PPV.version
WHERE A.productId IS NULL

So this seems like the answer to a slightly different question than you posed, but maybe it is equivalent. It all depends on what the possible versions might be.

 

[SQLBill]

example productid 1 has max version of 5.0

How do we know this? Do you enter this value somewhere? Your example doesn't have product 1 with a version of 5.0, 2.0 is the most recent.

-SQLBill

Posting advice: FAQ481-4875

 

[habesha]

Thank you all,

There is a little bit modification on the que

there is no version like 0.0, and one productid can have a redundent version
the table is like this

productId version

1 1.0
1 1.0
1 2.0
2 2.0
3 1.0
3 1.0
3 2.0
3 2.0
4 1.0

how can you modify your query to solve this

thanks again

 

[Catadmin]

The below should work. It unions the search for single line products with the search for multiple products, choosing the MAX version on the multi-line. Of course, MAX might change your results if Version is saved as an char or varchar field.

Select t1.ProductID, t1.Version
from MyTable t1
join (Select ProductID, Version
      from MyTable
      Group By ProductID, Version
      Having Count(ProductID) < 2) t2
on t1.ProductID = t2.ProductID
UNION ALL
Select t1.ProductID, Max(t1.Version) as Version
from MyTable t1
join (Select ProductID, Version
      from MyTable
      Group By ProductID, Version
      Having Count(ProductID) > 1) t2
on t1.ProductID = t2.ProductID
Group By t1.ProductID

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"