View Poll Results: .Net or Java?

Voters
0. You may not vote on this poll
  • .Net

    0 0%
  • Java

    0 0%
Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Post Unanswered: Querying one field, displaying all

    I have a products table containing the same product of various colors. As a simplified example, these three fields with these three records..

    .................................................. .................
    prod_name..........prod_desc..........prod_color
    .................................................. .................
    abc.....................abc in black........black
    abc.....................abc in white........white
    xyz.....................xyz in red...........red

    I only want to display each product name once. In my example, I want to only display abc once and xyz one. Here, the SQL statement would be...

    SELECT DISTINCT prod_name FROM tblProducts

    ...and I would get 'abc' and 'xyz'. The thing is, I would like to then display all of the fields within the DB, not just the prod_name field. I've tried the following statements, but none of these work...

    SELECT * FROM tblProducts WHERE EXISTS (SELECT DISTINCT prod_name FROM tblProducts)

    SELECT DISTINCT prod_name FROM tblProducts WHERE EXISTS (SELECT * FROM tblProducts)

    SELECT DISTINCT prod_name, prod_desc, prod_color FROM tblProducts

    What is the SQL statement I am looking for? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I would like to then display all of the fields within the DB, not just the prod_name field"

    there are two ways to interpret your question

    first, you could be after only one row from each prod_name, in which case, since you have multiple rows with the same prod_name field, such that you have to use DISTINCT on it, how are you going to decide which one of the multiple rows for each value of prod_name you want? the one with the lowest alphabetical description? color?

    the other way to interpret your question is that you want "styled" output, in other words, you want all rows returned, you just don't want to print the prod_name more than once

    something like

    Code:
    prod_name   prod_desc      prod_color
    abc         abc in black   black
                abc in white   white
    xyz         xyz in red     red
    if this is what you want, you need to do this with a reporting program

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    How is this information being displayed - through an ASP page using vbscript ? What do you want to do with this information once you have extracted it from the database ?

  4. #4
    Join Date
    Dec 2002
    Location
    NYC
    Posts
    8

    whoa...im trying to do the same thing

    I think what he was asking was this:
    I have the following table called Orders (i've numbered them to help the explaination)

    Ordnum Name City
    1 11111 Nick NYC
    2 11111 Nick ATL
    3 22222 Joe CHI
    4 33333 Joe CHI

    Now I want to select records based on the uniqueness of Ordnum only

    SELECT DISTINCT Ordnum from Orders;

    Lets say it returned records 1, 3, and 4. The following would be displayed:

    Ordnum
    1 11111
    3 22222
    4 33333

    But this is what I want to display:

    Ordnum Name City
    1 11111 Nick NYC
    3 22222 Joe CHI
    4 33333 Joe CHI

    So in short I want to display all of the fields but only have the DISTINCT statement apply to the Ordnum field, I don't care if Name and City are distinct or not.

    I'm thinking it should be something like the following but I can't figure out a way to make it work:
    SELECT *
    FROM Order
    WHERE Ordnum
    IN (SELECT DISTINCT Ordnum
    FROM Orders;

    Any help would be greatly appreciated...

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's a good description, except there's a tiny problem with your logic
    SELECT DISTINCT Ordnum from Orders;

    Lets say it returned records 1, 3, and 4. The following would be displayed:

    Ordnum
    1 11111
    3 22222
    4 33333
    the problem is that SELECT DISTINCT does not display rows from the original table, it displays values, and it returns these values as rows in a "new" table, the result set of the query

    so it would be better to say it returned

    47 11111
    48 22222
    49 33333

    where the "new" row numbers are different from the original table

    now, when you want to select rows from the original table, you can match them against this "new" table...

    ... but the big question is (and has always been) how do you exclude one of the 11111's and pick the other one? i mean, what determines which one you pick? because if you don't do anything about it, both values of 11111 in the original table will match the 11111 in the "new" table

    obviously, the answer is, you have to supply some other criterion

    like, for example, pick the row that has 11111 and also has the highest city name (in this case, NYC)

    once you specify that extra condition, it is easy to make a connection to the "new" table of distinct values
    Code:
    select Ordnum, Name, City,
      from Orders XX
     where City =
          ( select max(City)
              from Orders
             where Ordnum = XX.Ordnum )
    in this example, we use a correlated subquery to select the highest City name of all the rows that have the same Ordnum as the row being considered

    there is a way to do it with a join too, but i prefer the correlated subquery because it's very clear what it's doing

    rudy

  6. #6
    Join Date
    Dec 2002
    Location
    NYC
    Posts
    8
    wonderful, looks perfect...i was having trouble figuring out how to make the sub-query return 1 result...but still give me the columns i needed...never thought to use a fucntion to do this...hehe...

    I had considered the join method also but I was having trouble joining the same table together, and getting the results that I needed...I am assuming that the same method of using a fuction to return a single result would be the secret to making that work also?

    I'll test it out and let you know how I get along

    This doesn't apply to this situation since the query is for an Access database but here it is anyway.

    DISTINCT ON would allow me to select multiple fields but only have to distinct criteria apply to some of the fields correct?

    Thanks again!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, be careful with DISTINCT -- it is used to detect duplicate rows

    for example,

    SELECT DISTINCT X FROM ...

    if you select only one column, then the result set is only one column, in effect you are getting distinct values of X

    SELECT DISTINCT X, Y FROM ...

    if you select two columns, then the result set is two columns, and the DISTINCT operator guarantees distinct combination of values of X and Y

    i.e. distinct rows

    the most common sql error i've seen with regard to DISTINCT is assuming that it applies only to the first column in the list,

    SELECT DISTINCT X,
    A, B, C, D... FROM ...

    which, of course, will be distinct for all combinations of X, A, B, C, D...

    DISTINCT requires a sort

    just prior to being returned to you by the database, the result set is sorted on all columns -- this enables duplicate rows to be detected and elimininated



    rudy
    Last edited by r937; 12-09-02 at 15:47.

Posting Permissions

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