Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103

    Unanswered: SQL .. Group By ?

    Hi everyone !

    quick SQL challenge (at least it is - a challenge for me).

    Table A (fields: t_id, wo, so, insp, ..)

    Each group of records having the same [wo] has a unique record where [insp] = true (the rest of the records in the group has [insp]=flase.

    I would like to select all records that satisfy the following criteria:
    [so] > ([so] of the record that has [insp]=true)

    knowing that the criteria ([so] of the record that has [insp]=true) is different for different [wo]s

    it looked at first like a "group by" (on [wo]) solution is easy. But no success!


    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you create some ddl and sample data and expected results?


    Like:

    CREATE TABLE myTable99 (Col1 int, ect

    INSERT INTO myTable99 (Col1, ect
    SELECT 1, ect UNION ALL
    SELECT 1, ect UNION ALL
    SELECT 1, ect UNION ALL
    SELECT 1, ect

    RESULTS:

    1, ect
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    CREATE TABLE A(tind int, wo char(15), so int, insp bit)

    SELECT * from A where [wo]='W1'
    result:
    columns: tind,wo,so,insp
    1, 'W1', 5 , 0
    2, 'W1', 10 , 1
    3, 'W1', 15 , 0
    4, 'W1', 20 , 0

    SELECT * from A where [wo]='W2'
    result:
    columns: tind,wo,so,insp
    7, 'W2', 10 , 0
    9, 'W2', 12 , 0
    10, 'W2', 14 , 1
    15, 'W2', 20 , 0

    I would like my final query to return the following records:

    form the first set: (so>10) (notice [so] of 2nd record in first set)
    3, 'W1', 15 , 0
    4, 'W1', 20 , 0

    And from the second set: (so>14) (notice [so] of 3nd record in 2nd set)
    15, 'W2', 20 , 0

    So the Final result needed is:
    3, 'W1', 15 , 0
    4, 'W1', 20 , 0
    15, 'W2', 20 , 0

    Hope this helps clearing the problem.
    Thanks!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about something like:

    select A.* from (select wo, so from A where insp = 1) b join A on A.wo = b.wo
    where A.so > b.so

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you mean:

    Code:
    USE Northwind
    GO
    
    CREATE TABLE A(tind int, wo char(15), so int, insp bit)
    GO
    INSERT INTO A (tind, wo, so, insp)
    SELECT 1, 'W1', 5 , 0 UNION ALL 
    SELECT 2, 'W1', 10 , 1 UNION ALL 
    SELECT 3, 'W1', 15 , 0  UNION ALL
    SELECT 4, 'W1', 20 , 0  UNION ALL
    SELECT 7, 'W2', 10 , 0  UNION ALL
    SELECT 9, 'W2', 12 , 0  UNION ALL
    SELECT 10, 'W2', 14 , 1  UNION ALL
    SELECT 15, 'W2', 20 , 0 
    GO
    
    SELECT * FROM A WHERE wo = 'W1' AND so > 10
    UNION ALL
    SELECT * FROM A WHERE wo = 'W2' AND so > 14
    GO
    
    DROP TABLE A
    GO
    and if you posted the DDL and DML this way I would got it back faster...

    Can't what you're looking for though...right?

    EDIT: Right...just re-read
    Last edited by Brett Kaiser; 11-26-03 at 14:14.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's your criteria that makes you pick that s0 > 10 for W1 and s0 > 14 for W2?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am taking advantage of the fact that there is only one "true" record in each group of wo's. The join should take care of the rest, if that constraint holds.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    I am taking advantage of the fact that there is only one "true" record in each group of wo's. The join should take care of the rest, if that constraint holds.
    [doooooooh]
    It's alot easier to answer these darn things when you use your eyes...
    [/doooooooh]

    EDIT: Damn hangover
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Hi Brett!

    Sorry about the confusion. I am having trouble explaining the issue here.

    I am not using Northwind. I am using my own database.
    The table that i am interested in (let's call it Table A) has a long set of fields (the ones I am interested in are: [tind] int, [wo] char(15), [so] int and [insp] bit).

    Now .. this table "A" has a set of records (data smaples stated earlier)
    [wo]s: W1,W2 are samples .. the table hase 22000 records ..

    my delemma is: I would like to select a set of records that staisfy a condition that varries according to each group of [wo]s.

    In other words I would have to go through every group of records having the same [wo] and then find the record in that group that has [insp] = true and read its [so] (let's call it Active_so) .. then finally select the records that have [so] > Active_so. still confusing!
    Repeat that for all groups of [wo] in the table.

    (the data samples stated earlier shows 2 different sets and the ones that i would like my quey to return).

    I am not sure that the union would work.
    What I did is create a temp Table .. store all the records that satisfy my "varying" criteria and i was able to get the recordset needed.

    Still the problem is that I am using a stored procedure that does all the computing and adds the needed records to a temp table and returns the temp table to the client. But I can't modify that recordset from the client side since the temp table is not valid ouside the stored procedure.

    That's why i thought a Group By on [wo] might solve the problem and avoid using temp table to store result separate result sets.

    I know this is confusing.. I feel close to solving it .. but it's so frustrating.
    Thanks for your effort anyways.

    PS. In case you are still interested here is the a simlified protion of my stored procedure:
    --------------
    ..
    (CODE)
    --LOOP THROUGH ALL RECORDS ONE BY ONE
    FETCH NEXT FROM myRecordSet INTO @wo,@so,@insp

    WHILE @@FETCH_STATUS = 0
    BEGIN
    --FIND 'ACTIVE' RECORD FOR this [wo]
    SET @Active = (SELECT [so] FROM [A] WHERE [insp]=1 AND [wo] =@wo)
    IF @so > @Active INSERT @myTempTable(wo,so,insp) VALUES (@wo,@so,@insp)

    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM myRecordSet INTO @wo,@so,@insp
    END

    --RETURN THE CONTENT OF THE TEMP TABLE
    SELECT * FROM @myTempTable

    GO

    -------------

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    AccessUser, The Northwind database makes for a nice little playground to test out individual tables, joins, procedures, etc.. Brett's code is meant to be used to create a similar set of tables, so we can hammer away on them for a bit and understand better. Does that help?

  11. #11
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Originally posted by Brett Kaiser
    What's your criteria that makes you pick that s0 > 10 for W1 and s0 > 14 for W2?
    Criteria: insp=1

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by MCrowley
    AccessUser, The Northwind database makes for a nice little playground to test out individual tables, joins, procedures, etc.. Brett's code is meant to be used to create a similar set of tables, so we can hammer away on them for a bit and understand better. Does that help?
    Or for that matter .. the pubs database
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  13. #13
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Originally posted by Enigma
    There are 10 types of people in this world. Those that understand binary & those that don't.
    Enigma,
    "There are 10 types of people in this world. Those that understand binary & those that don't."

    That is by far the best quote I have seen in this forum! ) ..


    Happy holidays every body ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •