Select Distinct Values
Some values in my array are repeating. Is there a way to select distinct values from the script like you can in sql?
View Replies
ADVERTISEMENT
Distinct And Top In Select
I want to use a distinct and a top in a select statement, however I
want the distinct to only apply to one of the columns. For example
SELECT DISTINCT TOP 15 col1,col2,col3,col4 from table1
I only want the distinct to apply to say col2. Is there a way to do
this?
Select Distinct
i have a DB with table named Products. pCategory lists several names of teams of varing leagues (NBA, NFL, MLB, etc...) I want a table to display only the NBA teams. I have a field named pGroup which has NBA, MLB, etc... for each team. How can I display all fields where pGroup="NBA." I thought the code would work below, but it didn't.
<%sql_getProducts = "SELECT DISTINCT pCategory WHERE pGroup="NBA" FROM products"
SQL SELECT DISTINCT Problem
I have: Code:
sql = "SELECT DISTINCT LName FROM myTable ORDER BY userID"
But this produces an error saying you cannot use DISTINCT and ORDER BY in the same statement. What is the best way I can achieve a sorted list of unique values?
SELECT DISTINCT From Two Tables
I have two table which are related:
table1 holds personellinformation
table2 holds nodeInformation
The nodes in table2 can have a nodeOwner which will then get a recordID from
table1. A person can own multiple nodes.
Now I want to display all nodeowners, but not the duplicates (if they own
multiple nodes).
What would be the SQL syntax for that, using ASP with VB?
I've tried using DISTINCT, but I do also need the NodeID which is always
unique so I still get all duplicates.
Select Distinct Variable
I m trying to use select statement in order to select a distinct word like 'c', but in the database i only want to select c when it occurs on its own not when it appears in another word.
eg i need to select the alphabet 'c' in 'the cat category is c' but not the 'c' in 'cat' or 'category'. I have used the code below but this seem to count all 'c'
select set rsTypes2 = con.execute("SELECT COUNT(two) AS total FROM march where two like '%c%'")
Sql_getVAR = "SELECT DISTINCT
please bare with me here:
<%
dim VAR(2)
VAR(1)="nba"
VAR(2)="mlb"
sql_getVAR = "SELECT DISTINCT pcity FROM VAR"
Set getVAR = Conn.Execute(sql_getVAR) %>
I want several table according to the sport (nba,mlb, nfl etc...) I thought if i put the above code on an asp page (page02.asp) and then specified VAR on page01.asp with the link for NBA being /teams_02.asp?VAR=1 it would display the fields from the "nba" table.
Of course it did not saying the error was that VAR was not defined. Can someone tell me what I am doin wrong or if you understand it, explain a better way to me.
Function To Select 3 Random Numbers Which Are Distinct
I need to generate three random numbers between 1 and 100, but they need to be different.
e.g. 3,43,88 would be fine but 5,76,76 would not.
The function I have to generate a single random number is:
function funRandomNumber(intHighestNumber)
randomize
funRandomNumber = int(rnd * intHighestNumber) + 1
end function
called by: funRandomNumber(100)
Can anyone see how this can be modified to do what I need?
Count Records With Distinct Values
I have got an access database with 100's of records in it(each record is just one word/phrase)...i am looking to develop some asp code that will select all distinct values in that table and list the number of times that text appears.
For example in the table contains the following records: -
banana
apple
orange
apple
apple
orange
apple
I want the following output: -
apple - 4
orange - 2
banana -1
Splitting Values From A Select Box ?
I have this SELECT box which allows users to select multiple items to delete... however if they select 2 or more items... the values get passed over as 'one, two'
So the sql looks like Delete From Table Where Field='one, two';
How do i do it in such a way that it becomes:
Delete From Table Where Field='one';
Delete From Table Where Field='two';?
Select Box Option Values
I am creating an application in ASP (old school ASP not .NET) and VBSCRIPT connecting to a SQL Server 2000 back end. There is a form on my webpage that has a select box in which <option> tags allow users choose a document type.
Once the document type is determined it is sent off as a parameter in a stored procedure on the databse. The recordset returned holds the values that are required to go in another set of options boxes.
(the next set of option box values depend on the query results). I know it sounds easy...I mean I know how to do it if it where on 2 seperate pages (thats easy) where it passes on the info...but this is for ONE asp page.
SQL Select Not Picking Up Values
I'm having trouble getting a certain value out of my database. Code:
reportID = Request("reportID")
sql = "SELECT * FROM reports WHERE reportID =" & reportID
set rs=Server.CreateObject("ADODB.recordset")
set rs=conn.execute(sql)
I can get every value I want out except for one, rs("courses"), which is a text field that is filled from a multiple select form that enters the ID numbers of courses. The thing is, when I check the actual Access db file, I can see the value there (e.g: 1, 2, 3), but when I try to print it to my page (e.g: <% = rs("courses") %>),
I get nothing. I can't figure out why it won't work especially as I have an identical field in another table which I can get the value for using exactly the same method.
Anyone have any idea what could possibly going wrong?
How Can I Transfer Values From An Array To A Select Box?
I first retrieve the values stored in a session variable (where I have stored an array). Then I assign the session variable to a local array called "myarray".
Assume that "myarray" now contains 5 records. What would the code look like if I want to create a loop that transfers the 5 values from "myarray" into a select box/dropdown list?
How is that done?
Re-Selecting Values In Multi-select Menu
I use a multi-select menu in an ASP so the user can pick several values.
<select multiple name="lbErrorType" size="12">
However, after I have saved the records to the DB, and the user comes back
to edit a prior record, I re-populate the multiple select box and do a
"<option selected>" for each record that is saved to the DB.
<option selected>
But only the last value is selected !?!?
How do I get the select menu to select ALL the appropriate values ?
Alternative To <select> For Large Number Of Values?
My users have to select an value from a fixed selection of values. The
obvious choice of control for such a requirement is to use a <select> (i.e.
a combo box).
My problem is that sometimes, these combo boxes will have a *large* number
of values. There could be any number of values in them from 5 to 5 million
(unlikely it would be this large but possible).
Obviously 5 million is far too much to populate a <select> control with.
Does anyone have any suggestion as to how a user could select a value from a
*potentially* very large selection of values (i.e a suitable alternative to
the <select> control)?
Freeaspupload: Retrieving Multiple Values From A Select Field
I'm having problems retrieving all the values from a multiple selection field using the freeaspupload component.
I'm creating an online email app that uses the freeaspupload to add attachement functionality, which means the form encoding type is multipart/form-data. I've done something similar with the persits aspupload component, and had no problems with that, but the freeaspupload version seems to handle <select> fields slightly differently. Code:
Refresh Values In Select List Based OnChange Of A Dropdown Box
I am trying to learn and use good DHTML concepts and code (assuming they are needed). My current issue concerns how to handle this situation.
I have a Select List box which displays values based on what value is selected in a dropdown list. For example, my dropdown list contains a class name, and the Select list contains all of the students who are registered for that class.
So what I would like to occur, is when the user changes the class from the dropdown box, the Select List is re-created displaying the students who are enrolled in that class.
I thought I could do this without having to reload the page (with DHTML) but here is my problem. Code:
Using Distinct
I have a Drop Down box that is being populated using the SQL Distinct
command. How do I get it so it doesn't show Null values?
Here is my SQL statement
sSQL = "SELECT DISTINCT Title FROM Everyone ORDER BY Title ASC"
Distinct
this is my query
SELECT distinct(prof.id_num),prof.name,education.educ_lev el,education.school,education.degree,agency_info.c ampus
FROM prof inner join education on prof.id_num=education.id_num inner join agency_info on education.id_num=agency_info.id_num where education.educ_level<>'course' and agency_info.campus='colle'
ORDER BY education.school
course="tertiary"
still it shows duplicate records. it displays secondary,primary record of the employee
Group By? Distinct?
When I just - SELECT DISTINCT atblProducts.ID AS ProductID, it selects distinct Product IDs but when I add other columns to the select, it doesn't select the ProductID as DISTINCT.
I've heard to try "GROUP BY..." Code:
A 'distinct' Problem
I am trying to draw out from the database a distinct artists in relation to a variable which is 'cat'.
strsql = "SELECT distinct art_Artist, cat_ID, art_Category, art_Title, art_BDescription, art_Price, art_PicFull " &_
"FROM eventDisplay WHERE art_Artist='" & cat & "' "&_
"ORDER BY art_Artist"
Whats happening is that at this moment cat = Lee Jones, and he is in the databse three times with exactly the same info except for one subject which doesnt matter in this case, therefore we only need to see his name once being pulled.
Im sure this code has worked before but it doesnt seem to be workign here, i thought maybe its because his name is entered into the database with a gap inbetween his first name and last, but im probably wrong...
Distinct Filter
I have information in a database with the following structure:
3 Divisions - each division has several districts. Each district has several regions and each region has several areas.
I have a recordset of every area with the region, district and division they belong to.
I have 4 drop down menus on my page - area region, district and division.
How can I filter my recordset before each dropdown, so it only has DISTINCT records
SQL Distinct Problem
Here is the code that just won't work
Set rsPlayers = cnnDB.Execute("SELECT DISTINCT Players.PlrEmail FROM Players INNER JOIN Sessions ON Players.PlrID = Sessions.SsnPlrID WHERE Sessions.SsnGmID = 14 AND Players.PlrEMail <> 'Unknown' ")
If I use SELECT * From Players I get duplicates. I only want unique emails for the query.
Distinct An Inner Statement
How can I make this inner statement distinct? I've got the coding as follows but it displays every record anyway. Any way around this?Code:
sql = "SELECT DISTINCT cheats.*, searchtable.title FROM cheats, searchtable WHERE cheats.pin=searchtable.pin"
Set rschancheats = Server.CreateObject("ADODB.Recordset")
rschancheats.Open sql, conn, 3, 3
Distinct Field
I am building a real estate listingwhere the vistor choses a city or cities and the results display the counties they are in. The problem I have encountered is that
I am trying to make search results from a query that displays the county only once even if the visitor has chosen many cities within the same county.
I problem is that when the county field is set to be distinct, the recordset pulls out only the first record of that group. Therefore, when a search is performed on that query it will not find a potenial county even if the city belongs to that county.
ORDER BY DISTINCT
I'm trying to get this SQL statement to work:
SQL = "SELECT distinct(catagory) FROM PicAlbum ORDER BY dateadded"
I keep getting this error:
ORDER BY clause (dateadded) conflicts with DISTINCT
Is it possible to do a ORDER BY using a distinct?
Distinct Dates
I have a table containing newsletter articles. Each article has a zone which is either a section, division, district or all, and a edition they belong to.
I have the following sql statement in my vbscript to give a list of newsletter edition dates if there are articles relating to the relevant section, division or district:
"SELECT DISTINCT (edition) AS editions, zone FROM t_newsletter WHERE ((zone='"+sec+"') OR (zone='"+dis+"') OR (zone='"+div+"'))"
the trouble is that if there is an article in november for the relevent section and also an article for the relevant district then that date appears twice - how can I stop this, or do I need to go into my database?
Distinct Statement
How can do double select or do I have to loop though? I would like to go an strSQL = * From main but I only want to have one person from each team in week x, team being a field. I could select distinct teams then loop and simply only Do while once per team however I'd like to put it all together but of course
Select Distinct TEAM Where Week = vweek
can not be done. Any ideas. I saw some thing about being able to have a second statement underneath in [] once but was I dreaming?
Distinct In Sql Not Working!
I have my piece of code like that...everything is working nice but when i retrieve values from the database, the DISTINCT part does not seem to work... Code:
View Replies View RelatedSelecting Count(distinct Value)
I want to select just one field to display using sql and I want to have another variable that counts how many fields of the same value there are. My code looks something like this but i'm getting an error...
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal. Code:
Display Distinct Records
i am inner joing two tables customer and program. if i use select distinct i still get duplicate records displayed. how can i display distinct records for a customer by cust_no but still pull fields from program.
my customer and program table are joined by the cust_no field but in the program table the customer may have 10 different programs.
I tried "Select DISTINCT customer.cust_no ,customer.lastname, customer.firstname, program.season inner join program on customer.cust_no = program.cust_no"
Selecting Distinct Intervals
I need help programming a search function. I want to narrow down a search function regarding time. I figure the best way to do this is to select a higher interval and a lower interval, and then have an sql function that grabs the times from within that range.Code:
sql = "SELECT DISTINCT place, time FROM bussystem WHERE time >= '"&timey&"'"
Set rscurrent = Server.CreateObject("ADODB.Recordset")
rscurrent.Open sql, conn, 3, 3
sql = "SELECT DISTINCT place, time FROM bussystem WHERE time <= '"&timey&"'"
Set rsrecent = Server.CreateObject("ADODB.Recordset")
rsrecent.Open sql, conn, 3, 3
Distinct Column Selection
I have used the follwing query before in SQL and its worked fine but not on an access database is the syntax different?Code:
strSQL = "SELECT Count(DISTINCT BID) as varAmount FROM Bookings Where BookedBy = '" & rsStaff("Name") & "'"
The Error i recieve is:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'Count(DISTINCT BID)'.