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 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    populating distinct values in a drop down from database

    hey ... i hv a slight problem which i think u guys can help with it easily ... i am populating values in a drop down box from access database and it works fine ... but i can DISTINCT values from the database ... i have tried to use distinct keyword in the sql statement but it was no use

    in the code below field(0) is the unique ticket IDs and field(2) is the dates of submission of tickets... i want the values of dates to not be repeating ... can anyone help wid tht ?

    Code:
    <select name="demandid" class="formdropdown" id="demandid">
                        <option>Please Enter Date</option>
    					<%
    					qry_extract_values="select distinct * from demandform where status='undefined' and flag=0"
    					set rs=con.execute(qry_extract_values)
    					while not rs.eof
    					%>
    					<option value="<%=rs.fields(0)%>"><%=rs.fields(2)%></option>
    					<%
    					rs.movenext
    					wend
    					rs.close
    					%>
    					<option value="alldatesincluded">All Dates Included</option>
                      </select>

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Your problem is with the SQL rather than the ASP; you're selecting all fields from your table. DISTINCT needs a field to be distinct against. Change your SELECT statement so that:
    1. It only pulls out the fields you're actually going to use
    2. The one you want to be DISTINCT comes after the word DISTINCT

    Oh, and use rs.fields("fieldname") - makes it much easier to see what's going on.

  • #3
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    DISTINCT() does not work the way you think it does.

    DISTINCT pulls every record which is different in any way. So, for example. if you have a timestamp on your records, then you will always have duplicates because the timestamps may be off by only a second, but it still is a unique record against all the rest.

    NEVER EVER, unless absolutely necessary, use SELECT *. SELECT DISTINCT * will never work.

    However, what you want to do is pull only the dates that are DISTINCT, not the IDs. Since the UNIQUE ID will always be different, but the dates may be the same DISTINCT WILL NOT WORK FOR YOU HERE.

    You would probably need to use a subquery here, or even possibly a CROSS_JOIN. I haven't used Access in a long time, but I think that subqueries are available, but CROSS_JOINs are not.

    I will have your subquery written for you in just a moment.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    OK - Sorry. When users call, Forums, unfortunately, get put on hold.

    So, here's a little data for you about DISTINCT.

    NOTE I use MySQL, so some of the commands are a little different in Access.

    Code:
    CREATE TABLE `test1` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `SubmDate` date default NULL,
      `status` varchar(45) default 'undefined',
      `flag` tinyint(3) unsigned default '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    INSERT INTO test1 (SubmDate) VALUES
    ('2007-10-04'),
    ('2007-10-05'),
    ('2007-10-06'),
    ('2007-10-07'),
    ('2007-10-07'),
    ('2007-10-08'),
    ('2007-10-08'),
    ('2007-10-09'),
    ('2007-10-10'),
    ('2007-10-11'),
    ('2007-10-11')
    
    SELECT DISTINCT ID, SubmDate FROM test1;
    
    +----+------------+
    | ID | SubmDate   |
    +----+------------+
    |  1 | 2007-10-04 |
    |  2 | 2007-10-05 |
    |  3 | 2007-10-06 |
    |  4 | 2007-10-07 |
    |  5 | 2007-10-07 |
    |  6 | 2007-10-08 |
    |  7 | 2007-10-08 |
    |  8 | 2007-10-09 |
    |  9 | 2007-10-10 |
    | 10 | 2007-10-11 |
    | 11 | 2007-10-11 |
    +----+------------+
    
    
    SELECT DISTINCT(SubmDate),ID FROM test1;
    +------------+----+
    | SubmDate    | ID |
    +------------+----+
    | 2007-10-04 |  1 |
    | 2007-10-05 |  2 |
    | 2007-10-06 |  3 |
    | 2007-10-07 |  4 |
    | 2007-10-07 |  5 |
    | 2007-10-08 |  6 |
    | 2007-10-08 |  7 |
    | 2007-10-09 |  8 |
    | 2007-10-10 |  9 |
    | 2007-10-11 | 10 |
    | 2007-10-11 | 11 |
    +------------+----+
    Do the two queries above look familiar?? You will also notice that the results look the same, too.

    So, let's update 2 records, just to have a little fun...

    Code:
    UPDATE test1 SET Status = 'defined', flag = 1 WHERE ID = 4;
    UPDATE test1 SET Status = 'defined', flag = 1 WHERE ID = 10;
    
    SELECT DISTINCT ID,SubmDate,Status,flag FROM test1;
    +----+------------+-----------+------+
    | ID | SubmDate   | Status    | flag |
    +----+------------+-----------+------+
    |  1 | 2007-10-04 | undefined |    0 |
    |  2 | 2007-10-05 | undefined |    0 |
    |  3 | 2007-10-06 | defined   |    1 |
    |  4 | 2007-10-07 | undefined |    0 |
    |  5 | 2007-10-07 | undefined |    0 |
    |  6 | 2007-10-08 | undefined |    0 |
    |  7 | 2007-10-08 | undefined |    0 |
    |  8 | 2007-10-09 | undefined |    0 |
    |  9 | 2007-10-10 | undefined |    0 |
    | 10 | 2007-10-11 | defined   |    1 |
    | 11 | 2007-10-11 | undefined |    0 |
    +----+------------+-----------+------+
    Hmm... No change. Why? DISTINCT looks at all rows and all columns in the SELECT.

    Now:

    Code:
    SELECT DISTINCT SubmDate FROM test1 WHERE flag=0 and `status`='undefined';
    
    +------------+
    | SubmDate   |
    +------------+
    | 2007-10-04 |
    | 2007-10-05 |
    | 2007-10-07 |
    | 2007-10-08 |
    | 2007-10-09 |
    | 2007-10-10 |
    | 2007-10-11 |
    +------------+
    That works, but we only retrieved the Submission Date (SubmDate), not the ID.

    Now, try this...:

    Code:
    SELECT ID, SubmDate FROM test1 WHERE `status`='undefined' AND flag=0 GROUP BY SubmDate;
    +----+------------+
    | ID | SubmDate   |
    +----+------------+
    |  1 | 2007-10-04 |
    |  2 | 2007-10-05 |
    |  4 | 2007-10-07 |
    |  6 | 2007-10-08 |
    |  8 | 2007-10-09 |
    |  9 | 2007-10-10 |
    | 11 | 2007-10-11 |
    +----+------------+
    Gets you the records you want, without DISTINCT!!

    Let me know if you have any other questions on DISTINCT or the GROUP BY clause.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

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