Results 1 to 7 of 7

Thread: Select Distinct

  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Select Distinct

    Hello,
    Could you please help me with something,

    i have
    field1, field2 , field3, field4
    1 a p1 ab
    5 b p5 abc
    10 b p5 abc
    10 b p5 abc

    and i want this

    1 a p1 ab
    5 b p5 abc

    Distinct the last 3 field ...


    i try something like...


    SELECT DISTINCT `field2`,`field3`, `field4`
    FROM (SELECT MIN(`field1`),`field2`,`field3`, `field4` FROM table
    GROUP BY `field1`) alias

    I try also with union... but.. no hope


    Thank you!

  2. #2
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    select distinct field2, field3, field4
    from table

    would give you:
    a p1 ab
    b p1 abc

    So do you want to match this result set with the full data set?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT MIN(field1)
         , field2
         , field3
         , field4 
      FROM daTable
    GROUP 
        BY field2
         , field3
         , field4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2011
    Posts
    5
    Perfect!
    Thank you!
    Last edited by justin06; 08-29-11 at 04:54.

  5. #5
    Join Date
    Aug 2011
    Posts
    5
    And if i want to use a subquery...


    SELECT MIN(field1)AS field1, field2, field3, field4

    FROM table1

    WHERE cond1, cond2 AND field1 IN (

    SELECT DISTINCT field1 FROM table1

    )



    GROUP BY
    field2, field3, field4



    first is distinct all except field 1, then min field 3



    EX:
    a b c d e f g h i
    1 11 3 4 5 6 7 8 9
    10 11 12 13 14 15 16 17 18
    100 100 100 100 100 100 100 100 100
    1 100 100 100 100 100 100 100 100


    1. Sort by first field (min)
    a b c d e f g h i
    1 11 3 4 5 6 7 8 9
    1 100 100 100 100 100 100 100 100
    10 11 12 13 14 15 16 17 18
    100 100 100 100 100 100 100 100 100


    2. Remove duplicate except first field.

    a b c d e f g h i
    1 11 3 4 5 6 7 8 9
    1 100 100 100 100 100 100 100 100
    10 11 12 13 14 15 16 17 18

    3. Remove duplicate on field 1
    a b c d e f g h i
    1 11 3 4 5 6 7 8 9
    10 11 12 13 14 15 16 17 18


    Voila!


    if i use in will give all result...
    if i put = ... error...

    Last edited by justin06; 08-29-11 at 05:04.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have no idea what your last post is trying to say

    how about using real table and column names, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2011
    Posts
    5
    Hallo,


    another example

    O_Id OrderDate OrderPrice Customer
    2 9/3/2008 300 Hansen
    4 10/23/2008 1600 Nilsen
    4 9/3/2008 300 Hansen
    5 8/30/2008 2000 Jensen
    5 10/4/2008 100 Nilsen
    1 11/12/2008 1000 Hansen



    1. order by min(O_ID) will give me


    O_Id OrderDate OrderPrice Customer
    1 11/12/2008 1000 Hansen
    2 9/3/2008 300 Hansen
    4 10/23/2008 1600 Nilsen
    4 9/3/2008 300 Hansen
    5 8/30/2008 2000 Jensen
    5 10/4/2008 100 Nilsen

    2. remove duplicate except "O_id"


    O_Id OrderDate OrderPrice Customer
    1 11/12/2008 1000 Hansen
    2 9/3/2008 300 Hansen
    4 10/23/2008 1600 Nilsen
    5 8/30/2008 2000 Jensen
    5 10/4/2008 100 Nilsen



    3. remove duplicate "O_id"


    O_Id OrderDate OrderPrice Customer
    1 11/12/2008 1000 Hansen
    2 9/3/2008 300 Hansen
    4 10/23/2008 1600 Nilsen
    5 8/30/2008 2000 Jensen


    Thant`s all



    In my query "SELECT DISTINCT `O_id` FROM will give me more than one result so i try with " IN ( SELECT ...)

Posting Permissions

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