query within a query?? 1

[toetag]

I've been playing with this for a few hours and i can't seem to grasp the obvious. I need to find out all the records that don't have a corresponding version 1.

Table: Documents
Field1: DocNumber
Field2: Version

DocNum Version
1234 1
1235 1
1235 2
1235 3
1236 1
1237 2
1238 1
1239 3
1240 1
1240 1

i should get 1237 and 1239 with the example above. I wouldn't be here asking for dumbie 101 training if I did though. Thanks for any help in advance.

 

[toetag]

Star goes to myself. i figured it out 5 mins after posting. i should have waited it out.

select docnumber, version
from Documents
where version > 1 and docnumber not in (select docnumber from Documents where version =1)

 

[dbomrrsm]

Have A star on me

DBomrrsm

 

[DBAWinnipeg]

Maybe I don't understand the query you're trying to get but wouldn't you get the same info if you did this?

Select docnumber, version
from Documents
where version <> 1

 

[SQLScholar]

no what that would give is all the ones without version 1... what was required was all the ones without a corresponding 1. i.e. you can have a one all on its own (without a 2 or 3 etc...) and that wouldnt be on the query but if there was a 2 and it doesnt have a 1 to go with it (i.e. there is a record of a version higher then 1, but there is no 1) then put it on the list.

I think this is what was meant

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[toetag]

Yeah, plank had the idea. Needed Documents that had versions > 1 but didn't have a version 1.

 

[SQLScholar]

i dont know if it would be possible, or what you want but could this be made to say.

inlcude in query if there is not the version before this one. I.e. if there is a version 1 & 3 it will bring up 3 because there was no version 2.

just curious.

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[toetag]

Now that's a thought.... granted it took me a bit of time to come up with the somewhat simple query above. If i can free up some time this afternoon, i might try to wrangle that as well.

 

[SQLScholar]

If your using transact url - you might be able to do a union query which is cycled through by a loop of some description, and then runs the above checking that X has X-1 upto say 100.

obviously this would be in transact SQL, if you have it.

Dan


----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[ESquared]

SELECT DocNum
   FROM (SELECT DocNum, DocMax = Max(Version) FROM Documents) D1
      INNER JOIN (
         SELECT DocNum, Cnt = Count(*)
         FROM Documents
         GROUP By DocNum
      ) D2 ON D1.DocNum = D2.DocNum
   WHERE DocMax > Cnt

This just lists all the documents which don't have at least as many versions as the max version. If you have duplicate version numbers it could miss some (a document with versions 1,1,3 would not be listed, but you can take care of that with primary key or unique constraint).

A variation would list all the document+version numbers which are missing a lower version number, or alternately, all the documents + missing version numbers.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ESquared]

hmmmm... we all do things more complicated than necessary sometimes, don't we?

SELECT DocNum
  FROM Documents
  GROUP By DocNum
  HAVING Count(*) < Max(Version)

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLScholar]

but i am presuming the latest version on some may be 20, others may be 2. Surely this wouldnt work!

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[iker3000]

Hi!

This might be a bit faster then using a subquery:

SELECT
docnumber,
version
FROM
Documnets
LEFT OUTER JOIN
(SELECT
docnumber
FROM
Documents
WHERE
Version = 1) AS Version_1
ON Documents.docnumber = Version_1.docnumber
WHERE
Version_1.docnumber IS NULL

OR you might try this one - this should be also much faster(this only gives docnumber, but you can join it with documents if you need version too)

SELECT
docnumber
FROM
Documnets
GROUP BY
docnumber
HAVING
MIN(version) != 1


Iker

 

[SQLScholar]

SELECT
  docnumber
FROM
  Documnets
GROUP BY
  docnumber
HAVING
  MIN(version) != 1

Thats an interesting idea - i didnt think of it like that. do you know if this works?

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[ESquared]

Dan,

Your last version won't show documents that have a 1 but are missing a 2.

but i am presuming the latest version on some may be 20, others may be 2. Surely this wouldnt work!

SELECT DocNum
  FROM Documents
  GROUP By DocNum
  HAVING Count(*) < Max(Version)

Did you actually try it? Surely I think it WILL work!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[maswien]

plank,

The question you posted isn't very clear to me, if you can post your question like toetag did, using a few lines sample data, and indicate which rows you want the query returns, thta will be very helpful for other people to understand your idea accurately.

Thanks