DISTINCT truncating a memo field


,

I've created an .asp page that returns results from a memo field...I need to use Select DISTINCT, but when I do, the memo field gets truncated. When I remove the DISTINCT, the full memo field is displayed, but of course, I get too many records.

Has anyone encountered this before

Posted On: Tuesday 20th of November 2012 12:17:47 AM Total Views:  73
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Group distinct rows values to columns

I have a table that records the sales of several shops like this site|date|mount shop1|2012-04-04 08:05:02|10.5 shop1|2012-04-04 08:05:02|2.00 shop2|2012-04 08:05:02|7.50 shop1|2012-04-05 08:05:02|6.00 shop1|2012-04-05 08:05:02|9.00 shop2|2012-04-06 08:05:02|10.00 shop2|2012-04-06 08:05:02|3.00 I want make a report that shows on the columns the distinct dates of period and the distinct shops on the rows. site|2012-04-04|2012-04-05|2012-04-06 shop1|12.50|13.00|0 shop2|7.50|0.00|13.00 It's obvious that I have group by date by date and shops but I need help to do it cause I don't really know how.
VIEWS ON THIS POST

119

Posted on:

Monday 29th October 2012
View Replies!

MySQL Query Help - Trying to count distinct occurences of a record in another table

I have two tables: name: Classes ClassID ClassName ClassroomID ClassTime name: Classrooms ClassroomID ClassroomNumber Table Classrooms pretty much is static, where as table Classes changes often. Example data for classes: 001 Algebra 101 15 12:00 - 002 Geometry 101 14 12:00 - 003 Calculus 101 14 13:00 - 004 Calculus 101 14 15:00 - 005 Calculus 101 15 15:00 - 006 Physics 101 14 15:00 As you can see you can have the same class occurring at the same time in two different classroom, or varying classes occurring over different times in the same classroom. Just lik real life. What I want to do: Get a list and count of all distinct classes for each classroom. How many different types of classes in each classroom I do not need to know how many classes there are for each Based on the data above the results would be: ClassroomID|count 15|distinct classes is 2 (calc & algebra) 14|distinct classes is 3 (calc, geometry & physics) I can count total classes in each classroom but can't figure out number of distinct clases in each classroom.
VIEWS ON THIS POST

97

Posted on:

Monday 29th October 2012
View Replies!

Query - distinct.. order by count DESC

I want to count the users country wise, DESC by count. Example USA 25566 UAE 12344 Aus 456 Please Help
VIEWS ON THIS POST

90

Posted on:

Monday 29th October 2012
View Replies!

Select distinct columns in left join query

I'm trying to count the number of returned distinct results from each column on a left join query with 4 tables. Code: SELECT a.ad_id, count( w.wl_id ) AS wildnr, count( s.ds_id ) AS sitenr, count( o.op_id ) AS opnr FROM tblads a LEFT JOIN tblwild w ON a.ad_id = w.ad_id LEFT JOIN tblops o ON a.ad_id = o.ad_id LEFT JOIN tblsite s ON a.ad_id = s.ad_id WHERE a.ad_id = 247 GROUP BY a.ad_id, w.wl_id, s.ds_id, o.op_id But it counts all the returned rows (of course) of all the returned possible combinations (of course)! But the question is how can I get the three numbers from all returned disctinct results of each column Now the result is (for example): id name accom favfruit ------------------- 1 john house apple 2 john house orange 3 john house pear 4 john farm apple 5 john farm orange 6 john farm pear Which results in the count of: name = 6, accom = 6, favfruit = 6 While what I want is: name = 1, accom = 2, favfruit = 3 Anyone any idea
VIEWS ON THIS POST

210

Posted on:

Monday 29th October 2012
View Replies!

selecting distinct rows

I have a query which returns rows containing about 10 columns. One of these columns contains information repeated between 0 or more other rows. I would like to only return one row (the first preferrably). I have tried to use the distinct keyword however it does not work in this situation. A simplified example: when not using distinct like this: SELECT col1, col2, col3 FROM ..... I get: foo | bar | blah | foo | bat | gur | foo | hat | grips | But when I use the query: SELECT DISTINCT(col1), col2, col3 FROM ... I get the exact same output, eg the DISTINCT even with the brackets does not eliminate duplicate rows on col1. I suppose this is because the other columns are not distinct. Is there a way to do this Hope that makes sense and any help would be greatly appreciated!
VIEWS ON THIS POST

145

Posted on:

Monday 29th October 2012
View Replies!

Get distinct records where a field is largest

hello, I've created the following query. Code: SELECT q1.TEXT, q1.level FROM (SELECT m3.TEXT, 1 level FROM menu_permission_group m1, menu_permission_group_detail m2, menu m3, menu_permission m4 WHERE m1.id = m2.menu_permission_group_id AND m3.id = m2.menu_id AND m4.menu_permission_group_id = m1.id UNION ALL SELECT m1.TEXT, 2 level FROM menu m1, menu_permission m2 WHERE m1.id = m2.menu_id) q1 It returns the following record as shown bellow. http:// danimg.co.cc/images/42sql.jpg how can I only select unique TEXT fields where LEVEL is the highest E.g. in the e.g. above MENU 1, LEVEL 1 would not show as there is MENU 1 LEVEL 2.
VIEWS ON THIS POST

80

Posted on:

Monday 29th October 2012
View Replies!

Select distinct rows from list

-- Table: categories | id | category | ------------------ 1 5 1 6 1 9 2 3 2 6 2 9 3 7 3 8 3 9 ____________________________ I have a table like the above. Is there a way to select the ids that has, for example, both the categories 6 and 9. The result I'm looking for would be: + id + ------- 1 2 and not + id + ------- 1 1 2 2
VIEWS ON THIS POST

255

Posted on:

Monday 29th October 2012
View Replies!

Changing an sql query to only find distinct results

Code: SELECT u.user_id, u.deck, u.name, u.status, u.url, u.ID FROM ( SELECT ID, user_id, deck, name, status , url, count( * ) AS user_count FROM user_inventory WHERE url NOT LIKE '%00.png' AND name = '".mysql_real_escape_string($_COOKIE['mttcg']['u'])."' AND status NOT IN ('mastered', 'double') GROUP BY user_id, deck ) AS u INNER JOIN ( SELECT deck, count( * ) AS card_count FROM cards GROUP BY deck ) AS c ON u.deck = c.deck WHERE u.user_count = c.card_count That's the SQL code i'm currently using which isn't doing what i want it to do What its supposed to do is find out how many cards a user has then compare them to how many cards the set has The problem is if someone has 21 cards, then the query brings back no results. How would i modify the query so that it it detects to see if you have all the cards, event if you have doubles of a certain card So with this situation Code: INSERT INTO `user_inventory` VALUES(3488, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru02.png', 'Mamoru 02', 'mamoru', 7, 4160, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3489, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru03.png', 'Mamoru 03', 'mamoru', 7, 4161, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3490, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru05.png', 'Mamoru 05', 'mamoru', 7, 4163, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3493, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru15.png', 'Mamoru 15', 'mamoru', 7, 4173, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3494, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru18.png', 'Mamoru 18', 'mamoru', 7, 4176, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3495, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru20.png', 'Mamoru 20', 'mamoru', 7, 4178, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3536, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru01.png', 'Mamoru 01', 'mamoru', 7, 4159, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3537, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru04.png', 'Mamoru 04', 'mamoru', 7, 4162, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3538, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru06.png', 'Mamoru 06', 'mamoru', 7, 4164, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3539, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru07.png', 'Mamoru 07', 'mamoru', 7, 4165, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3540, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru08.png', 'Mamoru 08', 'mamoru', 7, 4166, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3541, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru09.png', 'Mamoru 09', 'mamoru', 7, 4167, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3542, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru10.png', 'Mamoru 10', 'mamoru', 7, 4168, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3543, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru11.png', 'Mamoru 11', 'mamoru', 7, 4169, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3544, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru12.png', 'Mamoru 12', 'mamoru', 7, 4170, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3545, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru13.png', 'Mamoru 13', 'mamoru', 7, 4171, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3546, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru14.png', 'Mamoru 14', 'mamoru', 7, 4172, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3547, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru16.png', 'Mamoru 16', 'mamoru', 7, 4174, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3548, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru17.png', 'Mamoru 17', 'mamoru', 7, 4175, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(3549, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru19.png', 'Mamoru 19', 'mamoru', 7, 4177, 'collecting', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(6320, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru10.png', 'Mamoru 10', 'mamoru', 7, 4168, 'keeping', 1, 20, 'character', ''); INSERT INTO `user_inventory` VALUES(7599, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru04.png', 'Mamoru 04', 'mamoru', 7, 4162, 'double', 1, 20, 'character', 'HangMan'); INSERT INTO `user_inventory` VALUES(8032, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru12.png', 'Mamoru 12', 'mamoru', 7, 4170, 'double', 1, 20, 'character', 'Add Card'); It'd pick up the set was complete, but not pick up the last 3 results since they are doubles of existing cards, since at the moment, any double cards have to be set to doubles for it to work. I've tried to get this working mysql, but it still never picks anything up I hope i've explained ok, i'm not good at explaining things
VIEWS ON THIS POST

71

Posted on:

Monday 29th October 2012
View Replies!

Question:Select distinct

i have a table with 4 columns and i want to get distinct from 2 columns i tried: select distinct col1,col2 from table1 ------------------- select distinct col1, distinct col2 from table1 -------------------- select distinct (col1,col2) from table1 and the results are duplicated values i searched whole google but i couldn't find any answer could u plz help me
VIEWS ON THIS POST

44

Posted on:

Monday 29th October 2012
View Replies!

Pivot table, distinct count, 1 to many relation

Hi , I am building a pivot table query. My problem is that the pivot columns don't report the correct numbers because I have to use a SUM(IF ...) AS pcolumnX expression when I have more than one pivot column. (When there's only 1 pivot column, it's not really a pivot table and I use COUNT(DISTINCT...).) I have a subquery that produces 3 columns: (seq_id,type, id). To simplify the below query, I'll refer to it as $seqs. Code: SELECT COALESCE(g.go_acc,'NO ANNOTATION') AS Term, g.ontology AS Ontology, g.go_desc AS Description, SUM(IF(seqlist.type='cr' AND seqlist.id=1,1,0)) AS cr1 SUM(IF(seqlist.type='cr' AND seqlist.id=2,1,0)) AS cr2 SUM(IF(seqlist.type='lib' AND seqlist.id=2,1,0)) AS lib1 FROM ($seqs) seqlist JOIN autofact a ON (a.seq_id=seqlist.seq_id AND a.pid IS NULL) LEFT JOIN autofact_go ag ON (a.af_id=ag.af_id) LEFT JOIN GOlist g ON (g.go_id=ag.go_id) GROUP BY Term ORDER BY Ontology,Term The above works fine if $seqs and autofact are 1 to 1. As soon as there are multiple records in autofact for each record in $seqs, I end up with redundant counts. Any thoughts on how to handle this
VIEWS ON THIS POST

124

Posted on:

Monday 29th October 2012
View Replies!

What is opposite of distinct in sql ?

i have over 5000 records .... some of my visitors have registered more than once using different names but same email. I have only one table (members table). I want to delete all the repeated records , and to do that i have to search for all those repeated entries. it is the opposite of distinct i know but i realy don't know the sql command for that. any idea please
VIEWS ON THIS POST

53

Posted on:

Monday 29th October 2012
View Replies!

sql distinct

hi there i have a query: Select HotelID, RoomNo, Description from Hotel Can I have a similar query where only the HotelID is distinct
VIEWS ON THIS POST

46

Posted on:

Wednesday 7th November 2012
View Replies!

Help - selecting all rows that contain one distinct column

, I need a query that will return rows that are distinct by one Column(CD.Title). I am using two tables User and CD. The results look something like this when I perform a select joining the two. CD.ID| CD.userID | CD.Title | CD.Description| User.Name| 1 40 xxxx zzzz Jason 2 40 yyyy aaaa Jason 3 40 xxxx nnnn Jason 4 40 xxxx xxxxx Jason The Query looks like this SELECT CD.cdID, CD.UserID, CD.Title, CD.Description, User.Name FROM User, CD WHERE CD.UserID = User.userID AND CD.UserID = 40 How do I get my query to return only CD.ID| CD.userID | CD.Title | CD.Description| User.Name| 2 40 yyyy aaaa Jason 4 40 xxxx xxxxx Jason I only want it to return rows that contain the last CD.Title that is unique. Im using MSSQL.
VIEWS ON THIS POST

99

Posted on:

Wednesday 7th November 2012
View Replies!

distinct and sum

gang. I have a question or two here. i am using access db with asp scripting. I need to pull 2 things using a query. distinct(username) and sum(iscore) and have it sort by iscore. i tried this. set name = conn.execute ("select distinct(username), sum(iscore) as pnts from rounds where iweek = " & var1 & " order by pnts desc") but i get the following: '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(sum(iscore) as pnts)'. /roughriders/PGA/main.asp, line 62 is there a way to do this what i have is a table that members enter scores. the fields are username iweek iscore so what i am doing is displaying the names, with the total of iscore where the iweek matches the criteria please help if you can Jeff
VIEWS ON THIS POST

74

Posted on:

Saturday 10th November 2012
View Replies!

distinct record

good morning. i am writing to you to ask for your advice and if you could give me please some direction on my technical problem regarding sql. we have got tables in access: persons_table id name 1 peter 2 paul 3 vince 4 carol fruits_table id fruits 1 banana 2 apple 3 orange 4 grapes person_fruits_table id_persons id_fruits 1 1 1 2 1 4 2 2 2 4 3 4 4 1 4 2 4 3 my problem is that, how can i manage to get a recordset that will give me one 1 for person and give the 1, 2 and 4 for fruitsone 2 for person and 2,4 for fruits one 3 for person and one 4 for fruits and one 4 and 1,2,3 for fruits i have tried using 'distinct' in sql statement in the person_fruits_table but it is still showing three 1's, two 2's, and three 4's. i'm not expecting you to answer my question directly but if you could please point me to the right direction please.
VIEWS ON THIS POST

48

Posted on:

Saturday 10th November 2012
View Replies!

One distinct col per row with n columns in result.

I have an table with n columns having repetitive col1 rows. i.e. ID Rem V_No Value Dr/Cr code 2 Null 1 2000 dr 2 2 a 1 500 cr 12 2 Null 1 1500 cr 104 5 3 600 cr 104 5 3 150 dr 2 5 a 3 450 dr 2 . . . and so on .. I Need result Like this ----- ID Rem V_No Value Dr/Cr code 2 Null 1 2000 dr 2 5 3 600 cr 104 . . . and so on .. Only first row having distinct ID with all those columns . Though , I am fresher in RDBMS (SQL-Server 2005) But I tried distinct, left inner join, in , exists .... But all fails to return reqd. result . Please help ...
VIEWS ON THIS POST

58

Posted on:

Monday 12th November 2012
View Replies!

How to get distinct values and its group of value/s?

I am working on heirarchy structure of an organization. Database: SQL Server 2005 Tables: OrgStructure, Employee OrgStructure.Manager = Employee.UniqueIdentifier OrgStructure.Subordinate = Employee.UniqueIdentifier So I built a query like this: "SELECT ORGSTRUCTURE.DisplayName, ORGSTRUCTURE.Manager, EMPLOYEE.DisplayName, EMPLOYEE.EmpID, EMPLOYEE.FirstName, EMPLOYEE.LastName FROM DBO.ORGSTRUCTURE ORGSTRUCTURE INNER JOIN DBO.EMPLOYEE EMPLOYEE ON ORGSTRUCTURE.Subordinate = EMPLOYEE.UniqueIdentifier INNER JOIN DBO.EMPLOYEE EMPLOYEE_1 ON ORGSTRUCTURE.Manager = EMPLOYEE_1.UniqueIdentifier" Now I am trying to build an HTML table to show the Managers in the 1st column of the table. For each row of a manager, I want to show the subordinates for that manager. Then go the next row. M SQL query shows multiple rows of a person, followed by the next person. It is possible because for each subordinate, associated manager is displayed - hence the redundant values. But how can I show the distinct values of the manager in the HTML table without using the DISTINCT keyword in SQL and their subordinates
VIEWS ON THIS POST

61

Posted on:

Monday 12th November 2012
View Replies!

Query output based on distinct column-values

I have 3 tables in the databased with the columns and Primary Key(PK) as follows- Customer(ssn,cname,age,profession); PK:ssn Vehicle(vid,vname,make); PK:vid Sale(ssn,vid,saledate); PK: ssn,vid Could anyone help with the following query Find all pairs of SSNs of different customers who have bought the same vehicle(can be any vehicle) of make 'Ford'.The output should NOT contain (ssn2,ssn1) if (ssn1,ssn2) is part of the output.
VIEWS ON THIS POST

259

Posted on:

Monday 12th November 2012
View Replies!

need help with complicated select distinct query

I have a table called gms_tests that keeps track of the results of tests run against servers. Columns are hostname, testname, status and time. The tests are run (about 5 against each server) every half hour, and the results are put in the table. I need to be able to search this table and obtain the most recent result for each test. For example, I need something similar to: select distinct hostname,testname from gms_tests order by time desc but I also need to be able to see the time and status columns. I've tried something like this: select hostname,testname,status,max(time) from gms_tests group by hostname,testname but the data returned in the status column is not correct - it doesn't match the status that corresponds with the latest test time. Any ideas what might work I'm using MySQL version 3.23.58, so I can't use subqueries.
VIEWS ON THIS POST

384

Posted on:

Monday 19th November 2012
View Replies!

how can select distinct records within loop..??

Originally posted by : dev (dev0505@rediffmail.com)i wanna select distinct records within loop..i have got normalize database which carry one user table(which carryes unique records) and another user's skills(repeatetion of records) i made a search on user's skill table and stored in a record set(repeatation is there in the record set) query is1.got search result in record set called rs:userid = rs("userid")while not rs.eof userid = rs("userid")set rss = con.execute(select distinct(user) from user where userid = '"& userid &"')")rs.movenextwendwhen i execute this query it's does'nt give me distinct record i believe we cann't use distinct function in while loop bec'z every record is unique it self within loop. is there any way to select distinct records in loop \t\t\t
VIEWS ON THIS POST

136

Posted on:

Tuesday 20th November 2012
View Replies!