Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jul 2009
    Location
    Odense, Denmark
    Posts
    72
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Complicated query - maybe SELECT DISTINCT?

    Hi everybody,


    I have a problem. I need a query and I can't figure out how to write it. I figured maybe some of you clever guys can.

    I have a table that looks remotely like this:

    Code:
    id	type	jnr	hovedstol
    6	1	20208	33489.00
    8	1	20213	5744.00
    9	3	20213	5744.00
    10	4	20213	5744.00
    13	2	20216	4555.00
    14	3	20216	4555.00
    15	4	20216	4555.00
    16	1	20215	2681.00
    As you can see, some jnr appear more than once but with different type.

    My query needs to get me the rows distinct to jnr (so every jnr only comes once) and only the rows where type is the highest with the given jnrs.

    So the results with the above table should be:

    Code:
    id	type	jnr	hovedstol
    6	1	20208	33489.00
    10	4	20213	5744.00
    15	4	20216	4555.00
    16	1	20215	2681.00
    Every jnr only comes once, and only the one where type is the highest.

    My SQL-skills at this time only get me to where I'm thinking "Hmm it's probably something with SELECT DISTINCT..."

    Can someone help me with this humdinga?


    Thanks in advance,
    deafdigit

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    select max(id,),max(type),jnr
    from table
    group by jnr
    see no logic for id result, but then again this should return what you want. Can't figure logic for last column.
    Last edited by BubikolRamios; 10-10-2011 at 03:53 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    No, Bubikol. That might give a result where the id and type don't match. You can't get them independently.

    Code:
    SELECT T.* 
    FROM table AS T,
         ( SELECT jnr, MAX(type) AS maxtype
           FROM table
           GROUP BY jnr ) AS S
    WHERE T.jnr = S.jnr
    AND T.type = S.maxtype
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    CAUTION: If there are any "ties" in your table, that won't work.

    That is, if you had something like this
    Code:
    14	3	20216	4555.00
    15	4	20216	4555.00
    16	1	20215	2681.00
    17      4       20216   3135.22
    15 and 17 have the same jnr and same type, so you will get *BOTH* records in your output.

    If you need to break the ties (perhaps by then selecting the record with the higher id?), let me know.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jul 2009
    Location
    Odense, Denmark
    Posts
    72
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank you Old Pendant (and Bubikol Ramios too).

    The way I designed the table, I'm almost but certain there aren't any ties (fingers crossed).

    I'll report back when I try the queries out.

  • #6
    New Coder
    Join Date
    Jul 2009
    Location
    Odense, Denmark
    Posts
    72
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Okay so this works great, but I have one more thing I need implemented for the query.

    I have another table that looks like this:

    Code:
    jnr	slut
    20165	0
    20166	2
    20167	1
    20168	1
    20169	1
    I need the query you guys made me to cross-check the jnr field with this other table to see if the slut is set to 1. If slut is not set to 1 then it should not accept that row.

    I hope I'm making sense cause I'm confusing myself just writing the question


    I hope you guys can help me,
    Thanks in advance,
    deafdigit

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Trivial.
    Code:
    SELECT T.* 
    FROM table AS T,
         otherTable AS OT,
         ( SELECT jnr, MAX(type) AS maxtype
           FROM table
           GROUP BY jnr ) AS S
    WHERE T.jnr = S.jnr
    AND T.type = S.maxtype
    AND T.jnr = OT.jnr 
    AND OT.slut = 1
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    deafdigit (10-14-2011)

  • #8
    New Coder
    Join Date
    Jul 2009
    Location
    Odense, Denmark
    Posts
    72
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Great. Thank you very much, Old Pendant.


  •  

    Posting Permissions

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