Search Results

Search found 37012 results on 1481 pages for 'sql query'.

Page 195/1481 | < Previous Page | 191 192 193 194 195 196 197 198 199 200 201 202  | Next Page >

  • SQL query for getting data in two fields from one column.

    - by AmiT
    I have a table [Tbl1] containing two fields. ID as int And TextValue as nvarchar(max) Suppose there are 7 records. I need a resultset that has two columns Text1 and Text2. The Text1 should have first 4 records and Text2 should have remaining 3 records. [Tbl1] ID | TextValue 1. | Apple 2. | Mango 3. | Orange 4. | Pineapple 5. | Banana 6. | Grapes 7. | Sapota Now, the result-set should have Text1 | Text2 Apple | Banana Mango | Grapes Orange | Sapota Pineapple |

    Read the article

  • problem in fetching data from several tables in one query

    - by Mac Taylor
    hey guys in an attempt to union my querries into one query to database , now im in need of geting username of first poster and last poster of a topic in my forums here is my code to do as i told :: $result = $db->sql_query("SELECT t.*,p.*,u.* SUM(t.topic_approved='1') AS Amount_Of_Topics, SUM(p.post_approved ='1') AS Amount_Of_Posts FROM bb3topics t, bb3posts p, bb3users u GROUP BY t.topic_last_post_id ORDER BY t.topic_last_post_id DESC LIMIT 10 " ); while( $row = $db->sql_fetchrow($result) ) { $Amount_Of_Topics = $row['Amount_Of_Topics']; $Amount_Of_Posts = $row['Amount_Of_Posts']; $Amount_Of_Topic_Replies = $Amount_Of_Topic_Replies + $row['topic_replies']; $Amount_Of_Topic_Views = $Amount_Of_Topic_Views + $row['topic_views']; $topic_id = $row['topic_id']; $forum_id = $row['forum_id']; $topic_last_post_id = $row['topic_last_post_id']; $topic_title = $row['topic_title']; $topic_poster = $row['topic_poster']; $topic_views = $row['topic_views']; $topic_replies = $row['topic_replies']; $topic_moved_id = $row['topic_moved_id']; $topic_time = $row['topic_time']; $result2 = $db->sql_query( "SELECT topic_id, poster_id, post_time FROM bb3posts where post_id = '$topic_last_post_id'" ); list( $topic_id, $poster_id, $post_time ) = $db->sql_fetchrow( $result2 ); $result3 = $db->sql_query( "SELECT username, user_id FROM bb3users where user_id='$poster_id'" ); list( $uname, $uid ) = $db->sql_fetchrow( $result3 ); $LastPoster = "$uname"; $result4 = $db->sql_query( "SELECT username, user_id FROM bb3users where user_id='$topic_poster'" ); list( $uname, $uid ) = $db->sql_fetchrow( $result4 ); $OrigPoster = "$uname"; now i need to query all this together not in separated ones i tried using left join but didn't worked what mysql conjunction should i use ?!

    Read the article

  • deleting records from multiple tables at a time with a single query in sqlserver2005

    - by sudhavamsikiran
    Hi I wanna delete records from child tables as well as parent table with in a single query. please find the query given below. here response header is the primary table and responseid is the primary key. DELETE FROM responseheader FROM responseheader INNER JOIN responsepromotion ON responseheader.responseid = responsepromotion.ResponseID INNER JOIN responseext ON responsepromotion.ResponseID=responseext.ResponseID WHERE responseheader.responseid In ('67D8B9E8-BAD2-42E6-BAEA-000025D56253') but its throwing error . can any one help me to find out the correct query

    Read the article

  • Paramaterising SQL in SSIS

    - by Anonymouslemming
    Hi all, I'm trying to paramaterize some queries in SSIS. After some reading, it sounds like my best option is to create one variable that contains my base sql, another that contains my criteria and a final variable that is evaluated as an expression that includes both of these. I want to end up with an SQL query that is effectively UPDATE mytable set something='bar' where something_else='foo' So my first two variables have the scope of my package and are as follows: Name: BaseSQL Data Type: String Value: UPDATE mytable set something = 'bar' where something_else = Name: MyVariable Data Type: String Value: foo My third variable has a scope of the data flow task where I want to use this SQL and is as follows: Name: SQLQuery Data Type: String Value: @[User::BaseSQL] + "'" + @[User::MyVariable] + "'" EvaluateAsExpression: True In the OLE DB Source, I then choose my connection and 'SQL command from variable' and select User::SQLQuery from the dropdown box. The Variable Value window then displays the following: @[User::BaseSQL] + "'" + @[User::MyVariable] + "'" This is as desired, and would provide the output I want from my DB. The variable name dropdown also contains User::BaseSQL and User::MyVariable so I believe that my namespaces are correct. However, when I then click preview, I get the following error when configuring an OLE DB Source (using SQL command from variable): TITLE: Microsoft Visual Studio Error at Set runtime in DB [Set runtime in myDb DB [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Must declare the scalar variable "@".". (Microsoft Visual Studio) Can anyone advise what I'm missing or how I can resolve this please ? Thanks in advance!

    Read the article

  • Avoiding secondary selects or joins with Hibernate Criteria or HQL query

    - by Ben Benson
    I am having trouble optimizing Hibernate queries to avoid performing joins or secondary selects. When a Hibernate query is performed (criteria or hql), such as the following: return getSession().createQuery(("from GiftCard as card where card.recipientNotificationRequested=1").list(); ... and the where clause examines properties that do not require any joins with other tables... but Hibernate still performs a full join with other tables (or secondary selects depending on how I set the fetchMode). The object in question (GiftCard) has a couple ManyToOne associations that I would prefer to be lazily loaded in this case (but not necessarily all cases). I want a solution that I can control what is lazily loaded when I perform the query. Here's what the GiftCard Entity looks like: @Entity @Table(name = "giftCards") public class GiftCard implements Serializable { private static final long serialVersionUID = 1L; private String id_; private User buyer_; private boolean isRecipientNotificationRequested_; @Id public String getId() { return this.id_; } public void setId(String id) { this.id_ = id; } @ManyToOne @JoinColumn(name = "buyerUserId") @NotFound(action = NotFoundAction.IGNORE) public User getBuyer() { return this.buyer_; } public void setBuyer(User buyer) { this.buyer_ = buyer; } @Column(name="isRecipientNotificationRequested", nullable=false, columnDefinition="tinyint") public boolean isRecipientNotificationRequested() { return this.isRecipientNotificationRequested_; } public void setRecipientNotificationRequested(boolean isRecipientNotificationRequested) { this.isRecipientNotificationRequested_ = isRecipientNotificationRequested; } }

    Read the article

  • Nhibernate setting query time out period for commands and pessimistic locking

    - by Nagesh
    I wish to specify a specific command timeout (or LOCK_TIMEOUT) for an SQL and once this time out is reached an exception (or alert) has to be raised in nHibernate. The following is an example pseudo-code what I have written: using (var session = sessionFactory.OpenSession()) { using (var sqlTrans = session.BeginTransaction()) { ICriteria criteria = session.CreateCriteria(typeof(Foo)); criteria.SetTimeout(5); //Here is the specified command timout, eg: property SqlCommand.CommandTimeout Foo fooObject = session.Load<Foo>(primaryKeyIntegerValue, LockMode.Force); session.SaveOrUpdate(fooObject); sqlTrans.Commit(); } } In SQL server we used to achieve this using the following SQL: BEGIN TRAN SET LOCK_TIMEOUT 500 SELECT * FROM Foo WITH (UPDLOCK, ROWLOCK) WHERE PrimaryKeyID = 1000001 If PrimaryKeyID row would have locked in other transaction the following error message is being shown by SQL Server: Msg 1222, Level 16, State 51, Line 3 Lock request time out period exceeded Similarly I wish to show a lock time out or command time out information using nHibernate. Please help me to achieve this. Thanks in advance for your help.

    Read the article

  • Yahoo Query Language Problem

    - by Damiano
    Hello everybody! Today, I've started with Yahoo Query Language. I would use it to retrive stocks details, so I'm talking about Yahoo Finance. I think there is a bug on this language. This is my query: select * from yahoo.finance.quoteslist where symbol='@^GSPC' I ALWAYS get 51 results! it's impossible, take a look at: http://it.finance.yahoo.com/q/cp?s=^GSPC There are 500 results! I also tried some paging parameters. select * from yahoo.finance.quoteslist(50,30) where symbol='@^GSPC' (to get from 50 to 80) select * from yahoo.finance.quoteslist(100) where symbol='@^GSPC' (to get the first 100 results) select * from yahoo.finance.quoteslist where symbol='@^GSPC' limit 30 offset 50 but ALWAYS the last stock is: <quote symbol="BBY"> <Symbol>BBY</Symbol> <LastTradePriceOnly>41.03</LastTradePriceOnly> <LastTradeDate>5/7/2010</LastTradeDate> <LastTradeTime>4:00pm</LastTradeTime> <Change>-0.48</Change> <Open>41.35</Open> <DaysHigh>42.35</DaysHigh> <DaysLow>39.60</DaysLow> <Volume>14129531</Volume> </quote> Why do I have this kind of problem? Thank you so much for your support! (P.S. I've tested it on Yahoo YQL console)

    Read the article

  • Nhibernate Exception - Return types of SQL query were not specified

    - by Muhammad Akhtar
    I am executing SQL in hibernate and getting exception Return types of SQL query were not specified public ArrayList get(string Release, int DocId) { string query = string.Format("select ti.Id, (' Defect ' + cast(ti.onTimeId as varchar) + ' - ' + ti.Name) as Name from TrackingItems ti inner join DocumentTrackingItems dti on ti.Id = dti.ItemStepId inner join Documents doc on dti.DocumentId = doc.Id where ti.ReleaseId = '{0}' AND doc.TypeId = {1} and Doc.Name is null AND ti.Type = 'Defect'", Release, DocId); ISession session = NHibernateHelper.GetCurrentSession(); ArrayList arList = (ArrayList)session.CreateSQLQuery(query).List(); return arList; } When I run this query in SQL, it working fine. any idea what could be the issue? -------- Thanks.........

    Read the article

  • Finding the SQL output of a parameterized query

    - by Dan Monego
    I'm making a parameterized query using C# against a SQL server 2005 instance, and I'd like to take a look at the SQL that is run against the database for debugging purposes. Is there somewhere I can look to see what the output SQL of the parameterized command is, either in the database logs or in the Visual Studio debugger?

    Read the article

  • How can I automatically generate sql update scripts when some data is updated ?

    - by Brann
    I'd like to automatically generate an update script each time a value is modified in my database. In other words, if a stored procedure, or a query, or whatever updates column a with value b in table c (which as a pk column (i,j...k), I want to generate this : update c set a=b where i=... and j=... and k=... and store it somewhere (for example as a raw string in a table). To complicate things, I want the script to be generated only if the update has been made by a specific user. Good news is that I've got a primary key defined for all my tables. I can see how to do this using a trigger, but I would need to generate specific triggers for each table, and to update them each and every-time my schema changes. I guess there are some built-in ways to do this as SQL server sometimes need to store this kind of things (while using transactional replication for example), but couldn't find anything so far ... any ideas ? I'm also interested in ways to automatically generate triggers (probably using triggers - meta triggers, huh? - since I will need to update triggers automatically when the schema change)

    Read the article

  • Query to MySQL from c# returns System.Byte[]

    - by karthik
    I am using the below SP to return the value of Generated Insert statement and it works fine when executed in Query browser. When i try to get the value from C#, it give's me "System.Byte[]" as return value. When i try to get the value from MySql query browser, it give's me return value as : 'insert into admindb.accounts values("54321","2","karthik2","karthik2","1");' I guess the problem is with the single quotes of the returned value. Is it so ? DELIMITER $$ DROP PROCEDURE IF EXISTS `admindb`.`InsGen` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`( in_db varchar(20), in_table varchar(20), in_ColumnName varchar(20), in_ColumnValue varchar(20) ) BEGIN declare Whrs varchar(500); declare Sels varchar(500); declare Inserts varchar(2000); declare tablename varchar(20); declare ColName varchar(20); set tablename=in_table; # Comma separated column names - used for Select select group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')')) INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename; # Comma separated column names - used for Group By select group_concat('`',column_name,'`') INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename; #Main Select Statement for fetching comma separated table values set @Inserts=concat("select concat('insert into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');') as MyColumn from ", in_db,".",tablename, " where ", in_ColumnName, " = " , in_ColumnValue, " group by ",@Whrs, ";"); PREPARE Inserts FROM @Inserts; EXECUTE Inserts; END $$ DELIMITER ;

    Read the article

  • Query broke down and left me stranded in the woods

    - by user1290323
    I am trying to execute a query that deletes all files from the images table that do not exist in the filters tables. I am skipping 3,500 of the latest files in the database as to sort of "Trim" the table back to 3,500 + "X" amount of records in the filters table. The filters table holds markers for the file, as well as the file id used in the images table. The code will run on a cron job. My Code: $sql = mysql_query("SELECT * FROM `images` ORDER BY `id` DESC") or die(mysql_error()); while($row = mysql_fetch_array($sql)){ $id = $row['id']; $file = $row['url']; $getId = mysql_query("SELECT `id` FROM `filter` WHERE `img_id` = '".$id."'") or die(mysql_error()); if(mysql_num_rows($getId) == 0){ $IdQue[] = $id; $FileQue[] = $file; } } for($i=3500; $i<$x; $i++){ mysql_query("DELETE FROM `images` WHERE id='".$IdQue[$i]."' LIMIT 1") or die("line 18".mysql_error()); unlink($FileQue[$i]) or die("file Not deleted"); } echo ($i-3500)." files deleted."; Output: 0 files deleted. Database contents: images table: 10,000 rows filters table: 63 rows Amount of rows in filters table that contain an images table id: 63 Execution time of php script: 4 seconds +/- 0.5 second Relevant DB structure TABLE: images id url etc... TABLE: filter id img_id (CONTAINS ID FROM images table) etc...

    Read the article

  • LINQtoSQL: Query to return List<String>

    - by ctrlShiftBryan
    I have a LINQ query that returns some object like this... var query = from c in db.Customers where ... select c; Then I do this List<String> list = new List<String>(); foreach (ProgramLanguage c in query) { //GetUL returns a String list.Add(GetUL(c.Property,c.Property2)); } Is there a way to combine into something list this? var query = from c in db.Customers where ... select new { GetUL(c.Property,c.Property2) }).ToList<String>();

    Read the article

  • How Do I Escape Apostrophes in Field Valued in SQL Server?

    - by Mikecancook
    I asked a question a couple days ago about creating INSERTs by running a SELECT to move data to another server. That worked great until I ran into a table that has full on HTML and apostrophes in it. What's the best way to deal with this? Lucking there aren't too many rows so it is feasible as a last resort to 'copy and paste'. But, eventually I will need to do this and the table by that time will probably be way too big to copy and paste these HTML fields. This is what I have now: select 'Insert into userwidget ([Type],[UserName],[Title],[Description],[Data],[HtmlOutput],[DisplayOrder],[RealTime],[SubDisplayOrder]) VALUES (' + ISNULL('N'''+Convert(varchar(8000),Type)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Username)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Title)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Description)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Data)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),HTMLOutput)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),DisplayOrder)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),RealTime)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),SubDisplayOrder)+'''','NULL') + ')' from userwidget Which is works fine except those pesky apostrophes in the HTMLOutput field. Can I escape them by having the query double up on the apostrophes or is there a way of encoding the field result so it won't matter?

    Read the article

  • YAHOO QUERY LANGUAGE BUG!

    - by Damiano
    Hello everybody! Today, I've started with Yahoo Query Language. I would use it to retrive stocks details, so I'm talking about Yahoo Finance. I think there is a bug on this language. This is my query: select * from yahoo.finance.quoteslist where symbol='@^GSPC' I ALWAYS get 51 results! it's impossible, take a look at: http://it.finance.yahoo.com/q/cp?s=^GSPC There are 500 results! I also tried some paging parameters. select * from yahoo.finance.quoteslist(50,30) where symbol='@^GSPC' (to get from 50 to 80) select * from yahoo.finance.quoteslist(100) where symbol='@^GSPC' (to get the first 100 results) select * from yahoo.finance.quoteslist where symbol='@^GSPC' limit 30 offset 50 but ALWAYS the last stock is: <quote symbol="BBY"> <Symbol>BBY</Symbol> <LastTradePriceOnly>41.03</LastTradePriceOnly> <LastTradeDate>5/7/2010</LastTradeDate> <LastTradeTime>4:00pm</LastTradeTime> <Change>-0.48</Change> <Open>41.35</Open> <DaysHigh>42.35</DaysHigh> <DaysLow>39.60</DaysLow> <Volume>14129531</Volume> </quote> Why do I have this kind of problem? Thank you so much for your support! (P.S. I've tested it on Yahoo YQL console)

    Read the article

  • Weird MySQL behavior, seems like a SQL bug

    - by Daniel Magliola
    I'm getting a very strange behavior in MySQL, which looks like some kind of weird bug. I know it's common to blame the tried and tested tool for one's mistakes, but I've been going around this for a while. I have 2 tables, I, with 2797 records, and C, with 1429. C references I. I want to delete all records in I that are not used by C, so i'm doing: select * from i where id not in (select id_i from c); That returns 0 records, which, given the record counts in each table, is physically impossible. I'm also pretty sure that the query is right, since it's the same type of query i've been using for the last 2 hours to clean up other tables with orphaned records. To make things even weirder... select * from i where id in (select id_i from c); DOES work, and brings me the 1297 records that I do NOT want to delete. So, IN works, but NOT IN doesn't. Even worse: select * from i where id not in ( select i.id from i inner join c ON i.id = c.id_i ); That DOES work, although it should be equivalent to the first query (i'm just trying mad stuff at this point). Alas, I can't use this query to delete, because I'm using the same table i'm deleting from in the subquery. I'm assuming something in my database is corrupt at this point. In case it matters, these are all MyISAM tables without any foreign keys, whatsoever, and I've run the same queries in my dev machine and in the production server with the same result, so whatever corruption there might be survived a mysqldump / source cycle, which sounds awfully strange. Any ideas on what could be going wrong, or, even more importantly, how I can fix/work around this? Thanks! Daniel

    Read the article

  • Conditionally Summing the same Column multiple times in a single select statement?

    - by btollett
    I have a single table that shows employee deployments, for various types of deployment, in a given location for each month: ID | Location_ID | Date | NumEmployees | DeploymentType_ID As an example, a few records might be: 1 | L1 | 12/2010 | 7 | 1 (=Permanent) 2 | L1 | 12/2010 | 2 | 2 (=Temp) 3 | L1 | 12/2010 | 1 | 3 (=Support) 4 | L1 | 01/2011 | 4 | 1 5 | L1 | 01/2011 | 2 | 2 6 | L1 | 01/2011 | 1 | 3 7 | L2 | 12/2010 | 6 | 1 8 | L2 | 01/2011 | 6 | 1 9 | L2 | 12/2010 | 3 | 2 What I need to do is sum the various types of people by date, such that the results look something like this: Date | Total Perm | Total Temp | Total Supp 12/2010 | 13 | 5 | 1 01/2011 | 10 | 2 | 1 Currently, I've created a separate query for each deployment type that looks like this: SELECT Date, SUM(NumEmployees) AS "Total Permanent" FROM tblDeployment WHERE DeploymentType_ID=1 GROUP BY Date; We'll call that query qSumPermDeployments. Then, I'm using a couple of joins to combine the queries: SELECT qSumPermDeployments.Date, qSumPermDeployments.["Total Permanent"] AS "Permanent" qSumTempDeployments.["Total Temp"] AS "Temp" qSumSupportDeployments.["Total Support"] AS Support FROM (qSumPermDeployments LEFT JOIN qSumTempDeployments ON qSumPermDeployments.Date = qSumTempDeployments.Date) LEFT JOIN qSumSupportDeployments ON qSumPermDeployments.Date = qSumSupportDeployments.Date; Note that I'm currently constructing that final query under the assumption that a location will only have temp or support employees if they also have permanent employees. Thus, I can create the joins using the permanent employee results as the base table. Given all of the data I currently have, that assumption holds up, but ideally I'd like to move away from that assumption. So finally, my question. Is there a way to simplify this down to a single query or is it best to separate it out into multiple queries - if for no other reason that readability.

    Read the article

  • Found % character in a SQL query

    - by Jensen
    Hi, I've an SQL database and I would like to do a query who show all the datas containing the sign "%". Normally, to find a character (for example: "z") in a database I use a query like this : mysql_query("SELECT * FROM mytable WHERE tag LIKE '%z%'"); But here, I want to found the % character, but in SQL it's a joker so when I write: mysql_query("SELECT * FROM mytable WHERE tag LIKE '%%%'"); It show me all my datas. So how to found the % character in my SQL datas ? Thanks

    Read the article

  • Optimizing an embedded SELECT query in mySQL

    - by Crazy Serb
    Ok, here's a query that I am running right now on a table that has 45,000 records and is 65MB in size... and is just about to get bigger and bigger (so I gotta think of the future performance as well here): SELECT count(payment_id) as signup_count, sum(amount) as signup_amount FROM payments p WHERE tm_completed BETWEEN '2009-05-01' AND '2009-05-30' AND completed > 0 AND tm_completed IS NOT NULL AND member_id NOT IN (SELECT p2.member_id FROM payments p2 WHERE p2.completed=1 AND p2.tm_completed < '2009-05-01' AND p2.tm_completed IS NOT NULL GROUP BY p2.member_id) And as you might or might not imagine - it chokes the mysql server to a standstill... What it does is - it simply pulls the number of new users who signed up, have at least one "completed" payment, tm_completed is not empty (as it is only populated for completed payments), and (the embedded Select) that member has never had a "completed" payment before - meaning he's a new member (just because the system does rebills and whatnot, and this is the only way to sort of differentiate between an existing member who just got rebilled and a new member who got billed for the first time). Now, is there any possible way to optimize this query to use less resources or something, and to stop taking my mysql resources down on their knees...? Am I missing any info to clarify this any further? Let me know... EDIT: Here are the indexes already on that table: PRIMARY PRIMARY 46757 payment_id member_id INDEX 23378 member_id payer_id INDEX 11689 payer_id coupon_id INDEX 1 coupon_id tm_added INDEX 46757 tm_added, product_id tm_completed INDEX 46757 tm_completed, product_id

    Read the article

  • Query a stored procedure for it's parameter names and types

    - by ho1
    Is there any easy way to query a stored procedure (Oracle - PL/SQL) for what parameters it expects? I know that I can query USER_SOURCE to get the whole procedure but I'd then have to parse the whole procedure, and if the parameter is of type [table].[column]%TYPE I'd then have to query the table schema as well. Either using just sql or via ODP.Net.

    Read the article

  • Find the % character in a LIKE query

    - by Jensen
    Hi, I've an SQL database and I would like to do a query who show all the datas containing the sign "%". Normally, to find a character (for example: "z") in a database I use a query like this : mysql_query("SELECT * FROM mytable WHERE tag LIKE '%z%'"); But here, I want to found the % character, but in SQL it's a joker so when I write: mysql_query("SELECT * FROM mytable WHERE tag LIKE '%%%'"); It show me all my datas. So how to found the % character in my SQL datas ? Thanks

    Read the article

  • how can i pass parameter to linq query

    - by girish
    i want to pass parameter to linq query... public IEnumerable GetPhotos() { PhotoDBDataContext db = new PhotoDBDataContext(); var tProduct = db.Photos; var query = from p in db.Photos orderby p.PhotoId descending select new { p.Album, p.AlbumId, p.Description, p.Photographer, p.PhotographerId, p.PhotoId, p.Tags, p.Thumbnail, p.Url }; return query; } in above example "orderby p.PhotoId descending" is used, i want to use parameter in place of p.PhotoId is it possible...

    Read the article

  • SQL indexes for "not equal" searches

    - by bortzmeyer
    The SQL index allows to find quickly a string which matches my query. Now, I have to search in a big table the strings which do not match. Of course, the normal index does not help and I have to do a slow sequential scan: essais=> \d phone_idx Index "public.phone_idx" Column | Type --------+------ phone | text btree, for table "public.phonespersons" essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone = '+33 1234567'; QUERY PLAN ------------------------------------------------------------------------------- Index Scan using phone_idx on phonespersons (cost=0.00..8.41 rows=1 width=4) Index Cond: (phone = '+33 1234567'::text) (2 rows) essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone != '+33 1234567'; QUERY PLAN ---------------------------------------------------------------------- Seq Scan on phonespersons (cost=0.00..18621.00 rows=999999 width=4) Filter: (phone <> '+33 1234567'::text) (2 rows) I understand (see Mark Byers' very good explanations) that PostgreSQL can decide not to use an index when it sees that a sequential scan would be faster (for instance if almost all the tuples match). But, here, "not equal" searches are really slower. Any way to make these "is not equal to" searches faster? Here is another example, to address Mark Byers' excellent remarks. The index is used for the '=' query (which returns the vast majority of tuples) but not for the '!=' query: essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) = 'fr'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Index Scan using tld_idx on emailspersons (cost=0.25..4010.79 rows=97033 width=4) (actual time=0.137..261.123 rows=97110 loops=1) Index Cond: (tld(email) = 'fr'::text) Total runtime: 444.800 ms (3 rows) essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) != 'fr'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Seq Scan on emailspersons (cost=0.00..27129.00 rows=2967 width=4) (actual time=1.004..1031.224 rows=2890 loops=1) Filter: (tld(email) <> 'fr'::text) Total runtime: 1037.278 ms (3 rows) DBMS is PostgreSQL 8.3 (but I can upgrade to 8.4).

    Read the article

< Previous Page | 191 192 193 194 195 196 197 198 199 200 201 202  | Next Page >