populating distinct values in a drop down from database

[farrukhna]

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 ?

<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>

[Spudhead]

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.

[Daemonspyre]

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.

[Daemonspyre]

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.

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...

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:

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...:

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.