Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
I saw a previous request for help, but I did not see a solution (other than deleting and re-initializing the dump.
I'm copying a database from one server to another and I repetedly get this error when I try to restore from the .bak file I create on backup. The host machine for the database is a workstation (no RAID). The Destination does have RAID, but is funtioning properly (according to the SysOps and in testing file copies). I've tried copying the .bak file to several different drives several different times and I get the same error. I also tried to back-up a database on the Destination server and restore the .bak file to a newly created database and I get the same error.
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth) select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
I have a table that has a street number field. if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...) how can this be done.
I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this
I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.
For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!
Dear All i have a problem In Query Analyzer .When I create two diffrent table with two diffrent schemas dbo and guest but when i log on query analyzer and run query from guest schema's tahle it gives error object not found i think its some rights problem ..
We have an SSIS package which runs regularly throughout the day, on 15 minute intervals. This package is moving data between two SQL Server instances, performing some simple identity mapping along the way. The primary source table is large, and we want to move only those records which have changed since the last time the package ran, so we use VersionDatestamps in the table, and pick up the dataset to be transferred by using the pacakge execution time, and the (previously recorded by the package) last-run time.
The problem we are having, is that the initial dataset picked up by SSIS is often missing records. The missing records are clearly within the time window that the package queried for, though they are near the boundary (within a minute, of the boundary, but as much as 30 seconds away) and typically all have an identical version datestamp to each other, within a single execution of the package.
At first, we thought this was an issue with date precision, but we've both truncated the dates, and even expanded the time window, and we still experience the same phenomenon.
The stored procedure which is responsible for updating the records in question, runs as a previous step to the SSIS package, within a single SQL Server Agent Job.
Has anyone experienced anything similar, or have some recommendation on how to track the source of this down?
KEYIDGROUP 1 1 a 2 1 b 3 2 a 4 2 b 5 3 a 6 3 b 7 4 a 8 5 a
This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.
query should reveal answer should be missing the group b KEYID 7 4 8 5
I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B
HiI can't work with my sql server 2000,When I try to open a table I get the message:"The query cannot be executed because some files are missing or not registered.run setup again to make sure the required files are registered.I uninstall and install again (few time)But all the time i get this message.Does any one know who to fix it??ThanksEfrat
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
on executing the below query i am getting the following error
ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.
even though the order by clause is presenet in the nested query
SELECT t.[ProductId], Predict ([Association].[Product Basket],3) From [Association] PREDICTION JOIN SHAPE { OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} APPEND ({OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} RELATE [ProductId] To [ProductId] ) AS [Product] AS t ON [Association].[Product Id] = t.[ProductId] AND [Association].[Product Basket].[Product Id] = t.[Product].[ProductId]
If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.
I was using the MDX Query Builder to create MDX queries for a SSRS report. I'm not sure what happened, but when I tried to create another dataset against the cube, the "Drop Column Fields Here" and "Drop Row Fields Here" areas were no longer available for me to drop attributes onto.
I have restarted VS, rebooted, you name it, I've tried it (short of re-installing). Has anyone encountered this and how did you "fix" it.
BTW: In order to continue working, I decided to use ProClarity to build the MDX for me and when I tried to paste it into the MDX editor, I get the following error: "The query cannot be prepared: The query must have at least one axis. ..". So, as I've seen from other posts, you can't use "any" MDX in the MDX Query Builder.
We see an unusual case where a query seems to be taking a long time (more than 30 minutes) as shown by the Java thread dump (below) - however the SQL server DB does'nt show any corresponding Connection for the query at the lower layer.
The JDBC layer seems to be "in progress" as far as processing the results of the query are concerned. If the Connection was dropped or had a failure we should have seen a corresponding SQLException in the JDBC layer - which is also not the case.
Any tips on how to debug this? Is there a timeout set on the JDBC Connection which causes it to wait before it detects any failures?
thanks
"JMS Session Delivery Thread" daemon prio=6 tid=0x0000000006434780 nid=0x868 run nable [0x0000000020d6e000..0x0000000020d6f860] at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:129) at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep aredStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedState mentExecutionRequest.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source ) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unkno wn Source) - locked <0x00000000955574e0> (a com.microsoft.sqlserver.jdbc.TDSWriter)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate (Unknown Source) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del egatingPreparedStatement.java:101) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del egatingPreparedStatement.java:101) at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAc cessImpl.java:213) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistenc eBrokerImpl.java:2021) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:977) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:1014) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:884) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeAndLinkOneToOne (PersistenceBrokerImpl.java:1074) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeReferences(Pers istenceBrokerImpl.java:1050) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistenc eBrokerImpl.java:1981) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:977) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:1014) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:884) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati ngPersistenceBroker.java:220) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati ngPersistenceBroker.java:220) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati ngPersistenceBroker.java:220) at com.serus.db.ojbutils.SerusPersistenceBrokerHandle.store(SerusPersist enceBrokerHandle.java:178) at com.serus.common.OJBDAOUtil.store(OJBDAOUtil.java:636) at com.serus.dao.wip.AbsWipDAO.saveDTO(AbsWipDAO.java:251) at com.serus.dao.wip.tx.LotTXDAO.saveLotTx(LotTXDAO.java:56) at com.serus.manager.wip.txprocessing.AbstractTxProcessorImp.storeLotTx( AbstractTxProcessorImp.java:1380) at com.serus.manager.wip.txprocessing.LotTxProcessingMoveStage.storeLotT x(LotTxProcessingMoveStage.java:802) at com.serus.manager.wip.tx.LotTxManager.saveLotTx(LotTxManager.java:248 ) at com.serus.manager.wip.tx.LotTxXMLProcessor.parseMessageOriginal(LotTx XMLProcessor.java:137) at com.serus.manager.wip.tx.LotTxXMLProcessor.parseMessage(LotTxXMLProce ssor.java:78) at com.serus.events.listener.SerusJMSListener.onSerusMessage(SerusJMSLis tener.java:197) at com.serus.events.listener.AbstractEventListener.onMessage(AbstractEve ntListener.java:53) at progress.message.jimpl.Session.deliver(Unknown Source) at progress.message.jimpl.Session.run(Unknown Source) at progress.message.jimpl.Session$SessionThread.run(Unknown Source)
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one, which ones are in the first and third but not in the second, and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
I finally have my server linked, figured out my date issed with the decode statement and now I get a missing Expression error from Oracle.
Here is the statement:
SELECT * FROM OPENQUERY(PROD_ORACLE,' SELECT LEFT(CUST_ORDER_STUS_NME, 25) as CUST_ORDER_STUS_NME, LEFT(SRVC_ORDER_STUS_NME, 25) as SRVC_ORDER_STUS_NME, LEFT(Ser_ORD, 20) AS Ser_ORD, LEFT(CUST_ORDER_NME, 25) as CUST_ORDER_NME, LEFT(SRVC_ORDER_NME, 25) as SRVC_ORDER_NME, LEFT(CLS_ALLOWED_NBR, 2) as CLS_ALLOWED_NBR, LEFT(NOC_TO_NOC_NME, 3) as NOC_TO_NOC_NME, LEFT(CHARS_ID, 20) as CHARS_ID, LEFT(PRI_DNS_QTY, 5) as PRI_DNS_QTY, LEFT(SCNDY_DNS_QTY, 5) as SCNDY_DNS_QTY, LEFT(ORDER_TYPE_CD, 10) as ORDER_TYPE_CD, LEFT(ACTY_NME, 25) as ACTY_NME, replace(LEFT(CUST_A_NME, 50), ''|'', ''-'') as CUST_A_NME, LEFT(RLAT_CKT_ID, 8) as RLAT_CKT_ID, DECODE (BILL_CLR_DT, GREATEST(BILL_CLR_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), BILL_CLR_DT, NULL), DECODE (CMPLT_DT, GREATEST(CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), CMPLT_DT, NULL), REPLACE(replace(LEFT(CMNT_TXT, 1000), char(10), '' ''), ''|'', ''-'') as CMNT_TXT, LEFT(CUST_H1_ID, 9) as CUST_H1_ID, LEFT(CUST_CMS_ID, 8) as CUST_CMS_ID, LEFT(Circuit_ID, 30) as Circuit_ID, DECODE (SO_CMPLT_DT, GREATEST(SO_CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), SO_CMPLT_DT, NULL), LEFT(DNS_ADMIN_NME, 30) AS DNS_ADMIN_NME, LEFT(DNS_ADMIN_PHN_NBR, 15) AS DNS_ADMIN_PHN_NBR, LEFT(DNS_ADMIN_EMAIL_ADDR, 128) AS DNS_ADMIN_EMAIL_ADDR, LEFT(CONTACT, 30) AS CONTACT, LEFT(GOVT_CD, 10) AS GOVT_CD, LEFT(SOTS_ID, 30) AS SOTS_ID, LEFT(RAS_USER_BLK_NBR, 20) AS RAS_USER_BLK_NBR, LEFT(VSYS_QTY, 10) AS VSYS_QTY, LEFT(ZONE_QTY, 10) AS ZONE_QTY, LEFT(PLCY_QTY, 10) AS PLCY_QTY, LEFT(PIC_CODE, 6) AS PIC_CODE, LEFT(SO_Order_Entry_Nbr, 15) as SO_Order_Entry_Nbr, LEFT(SO_Rlat_Order_Entry_Nbr, 15) as SO_Rlat_Order_Entry_Nbr, '' '' as filler From PROD_ORACLE..RDBADM.CUST_SRVC_ORDER_V')
Here is Query Analyzers response:
Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'. [OLE/DB provider returned message: ORA-00936: missing expression] OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare:repare returned 0x80040e14].
I would like to have a parameter on my report to Select by Order_Taken_By. My attempt at that is here:
Dataset called OrderTaken:
select distinct SO_Header.Order_Taken_By from SO_Header Under the report Parameter Properties named @Order, under Available Values Selected "Get Values from a query"
I have DataSet: OrderTaken
Value Field: Order_Taken_By Label Field: Order_Taken_By
The values of the field Order_Taken_By is all text characters, no integer values.
Running the inital Query by itself yields results. When I add the parameter, I can make a selection, but now I get no results, even though there should be values for the choice I've chosen.
Could anyone help me insert in the first column a row number of this query: (notice that it has a UNION)
SELECTB.DOCDATE, B.DOCNUMBR, A.USERDEF2, (CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR, 0 AS INT_EXEN, 0 AS IMP_EXEN, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES, (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA, ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) + (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) + (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) + ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL FROMPM20000 B INNER JOIN PM00200 A ON B.VENDORID = A.VENDORID WHEREB.TAXSCHID >= 'PLAN IVA' AND B.TAXSCHID <= 'PLAN TRANSP.CON' AND B.VOIDED = 0 AND B.TAXAMNT <> 0 AND UNION ALL SELECT B.DOCDATE, B.DOCNUMBR, A.USERDEF2, (CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR, 0 AS INT_EXEN, 0 AS IMP_EXEN, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES, (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA, ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) + (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) + (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) + ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL FROMPM00200 A INNER JOIN PM30200 B ON A.VENDORID = B.VENDORID WHEREB.DOCTYPE <> 6 AND B.TAXSCHID >= 'PLAN IVA' AND B.TAXSCHID <= 'PLAN TRANSP.CON' AND B.VOIDED = 0 AND B.TAXAMNT <> 0 AND ORDER BY DOCDATE
Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.
happy friday... my table has 200,000 + records and I want to see the particular record which I think it is causing the problem. How could i query 195,203rd record? thank you, yanoroo
Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.
For example, I have the following records:
NumColumn 1 2 3
4 10 11
18
If I use "select Max(NumColumn) from aTable", I will get the number: 18.
But I need to use SQL to the second biggest number: 11.
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:
"In the forums there is X results for the word X In the articles there is X results...."
And when the user click one of those lines, the list shows the specific results in that section.
My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…
Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)
i am trying to write a query for phone number lookup . The query should be able to search numbers which have anything matching .... like if the person enters 1918767899 or enters 918767899 the query should be able to find both the records. Itried using the LIKE , but it doesn't work the way it is required.
