I have many record in my sql table and i want to fetch last row of table.I have used max function but it is very slow
i m using this query-
select pm_name from datos where date_time = (select Max(date_time) from datos) but it takes more time i want to use select count(date_time) from datos this query provide me row number then how can fetch row of this number.
In Sql Server, there's a Row_Number and now I want to do the same thing with LinQ when I get data from a table and I want to create a new row like Row Number.
View 3 RepliesI have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:
[Code]....
After I click the button "ObriĊĦi" (Delete) I receive the following error: "Must declare the scalar variable "@KategorijaID"." I understand that the problem is with parameter, but I don't know how to correct it to use it for both the "command" and "brisanje" SQLCommands.
I have a table students (studId, student) and 1000 records
1. I want to see in how many distinct name, thier frequequency
2. show only the records with frequency greater than 3
select a.* from (select vip,date,amount,dense_rank() over(order by vip,date) as 'sn' from income ) a where sn = 1
vip date amount sn
0001 2010/1/1 50 1
0001 2010/1/1 10 1
0001 2010/1/6 80 2
0001 2010/1/7 90 3
0002 2010/1/2 40 4
0002 2010/1/5 10 5
0002 2010/1/5 80 5
0002 2010/1/7 90 6
would it possible give it row number for each group? (group by vip) like this
vip date amount sn
0001 2010/1/1 50 1
0001 2010/1/1 10 1
0001 2010/1/6 80 2
0001 2010/1/7 90 3
0002 2010/1/2 40 1
0002 2010/1/5 10 2
0002 2010/1/5 80 2
0002 2010/1/7 90 3
How can I SELECT second if first SELECT returns nothing?
View 8 RepliesI used an function in select statement. the select return 3 rows but the function return same value in this 3 rows.
[Code]....
i have been having trouble trying to find out what is wrong with my sql select query... i wanted to select a sessionID by comparing the tourID and dates. the dates datatype at the database is datetime and the value of itemSessionDate is in string. i tried to convert itemSessionDate to date and dateAndTime but it gave me mm/dd/yyyy while the date in my database is dd/mm/yyyy hh:mm:ss. so i thought i can try using the itemSessionDate string as it has the same value as the one return from the database. but it keeps on goin to the if not reader.hasRows and quit the statement
[Code]....
I have been utilising a SELECT query such as the following:
SELECT SRFILE.SR_GROUP, SRFILE.SROWNERSHIP, SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,
BSFLBWF_1.Severityx, BSFLBWF_1.Openx, BSFLBWF_1.Closex, BSFLBWF_1.Closurex, BSFLBWF_1.Customerx, BSFLBWF_1.Availabilityx, BSFLBWF_1.KPIx,
CONVERT(Decimal, BSFLBWF_1.Minsx) AS Expr1, BSFLBWF_1.Unusedx, BSFLBWF_1.FirstIntx, BSFLBWF_1.RepeatIntx, BSFLBWF_1.Corex,
BSFLBWF_1.OffPeakx, BSFLBWF_1.NonCorex, BSFLBWF_1.DedTypex, CONVERT(money, BSFLBWF_1.SchoolChargex) AS Expr2, CONVERT(money,
BSFLBWF_1.LANMLEChargex) AS Expr3, BSFLBWF_1.KPIFailx, BSFLBWF_1.Overridex, BSFLBWF_1.Reasonx, BSFLBWF_1.Contactx,
SRFILE.SR_CLOCK, SRFILE.SR_BACK_REASON, CONVERT(int, BSFLBWF_1.Minsx) / CONVERT(decimal, BSFLBWF_1.FirstIntx)
* 100 AS PERCENTAGE
FROM support.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL) AND (BSFLBWF_1.DedTypex = 'School' OR
BSFLBWF_1.DedTypex = 'LAN-MLE')
UNION ALL
The rest of the query continues as the above albeit selecting the same data from different tables and works fine.
The issue I have is that I want to Select some more data (another SELECT query) but with a different Where clause e.g. WHERE (BSFLBWF_1.KPIx NOT LIKE 'KPI%' OR
BSFLBWF_1.KPIx NOT LIKE 'Proactive%') AND (BSFLBWF_1.Availabilityx LIKE '')
The problem is that this WHERE clause returns data which the above in bold has null values for (and thus doesnt work). Im still selecting the same data its just the WHERE Clause has changed. Is it possible to run such a query? If so, can it be done via a sub query?
I have an SQL 2005 db with a table named "industry_suppliers."
I've created a query to enable someone to search this table using 3 parameters.
The results need to be grouped by "product_description" (which is essentially a category). When displayed on an .aspx page, for example, the results need to look like this:
Apples
Acme Apple Company
Bradbury Orchards
Oranges
A1 Citrus Company
Belfast Growers, Inc
Bradbury Orchards
Notice how "Bradbury Orchards" is repeated under both categories. This is the result I want, EXCEPT in cases where someone wants to filter the search results by category. Let's say someone wants to search just for apple suppliers. The problem I'm having is that any record that appears under apples will also get listed again under any other categories associated with that record. So, searching for apple suppliers, the results are this:
Apples
Acme Apple Company
Bradbury Orchards
Oranges
Bradbury Orchards
I'm having trouble figuring out how to revise my query to avoid this problem.
[Code]....
I have partitioned a table and it has been partitioned according to an account id. ie also rows that have accountid =1 will be in A partition and all rows that have accountid = 2 will be in B partition. so now if i execute a query with where condition accountid =1 sql server will search only in A partition. Suppose if i add another condition to it ie where accountid=1 and place="aaa" will this query search only A partition or the entire table?
View 3 RepliesI have stored procedure which selects records from an SQL table based on a bunch of user-input parameters.Just discovered that if a record in the table has null values in some of the columns (I haven't figured out which ones yet), then the SELECT is not returning the record (even if it satisfies all the parameters).The SELECT statement is supposed to allow for nulls, and I've been over it a bunch of times and am not sure what I'm doing wrong.Or do I need to get rid of all the null values in the SQL table, and prevent new ones from being introduced?
[Code]....
i want to select three records order by Date .This is my News table.
NewID NewTitle NewContent NewDate
1 Example ExampleContent 01.12.2010
2 Example2 ExampleContent2 02.12.2010
3 Example3 ExampleContent3 03.12.2010
4 Example4 ExampleContent4 04.12.2010
So,query selects example4,example3 and example2.
I have an asp.net site which includes a "search" page that queries an SQL table, then redirects the user to a "results" page where the results of the query are displayed. The parameters for the query are assembled into a string which is the SELECT statement. I've been asked to add additional functionality that requires a secondary search which takes place on the "results" page. How can I pass the parameters from the "search" page to the "results" page so that I can access those parameters in the code behind? Here's the first page code behind (I inherited most of this from another developer):
[Code]....
And the second page--it would be on line 51 that I would need to insert the parameters from the search above:
[Code]....
how to find and replace substring in select query
DATA RELEASE FORM $LSB$DRF$RSB$
[Code]....
for some reason it doesnt return any values.
before i've added the inner join it worked perfectly.
i couldn't find my mistake, though i passed over it several times.
This is my Table structure:-Table1
Field1 Field2 Age
Dean Chen 20
Janel Wast 25
ann Miller 20
I used this select statement
select Field1+' - '+Filed2 from Table1
and i binded this in dropdownlist
now on selecting dropdownlist i need to show age in label. so how would be my select statement... to show age base on Filed1 and Filed2
I have inherited a very old SQL Server Database for a live site, but have been asked to add a new table for invoice recording to it. Fairly easy to do for any new record added from now on, however I run into some issues with selecting older records.I need to find a way to select from 3 tables, see if a record exists in the new table and if not insert a new record to it.My stored procedue so far is (The new table is [Invoicing]:
[Code]....
in my table data are stored many times ...I want select the only one data from the repeated data
select ven.Id,ven.Vendor_Name from Purchase_Requsition pur,Item_master itm,Approved_Vendors ven where itm.Item_Id=pur.Item_masterItem_Id and ven.Id=itm.Approved_VendorsId and pur.Request_StatusId=1 and pur.StatusId<>4
ID VENDOR_NAME
1 Ranbaxy
1 Ranbaxy
1 Ranbaxy
2 cipla
i want to make it thru query as
ID VENDOR_NAME
1 Ranbaxy
2 cipla
In my data table i have a Date Column with of type DateTime.
In my sql select query, I want to return all records where the date is today.
Is there a way to select data from multiple tables other than the UNION ALL statement. Addtionally I need to select data from a different database.
My current statement is as follows (there are alot more fields but I have shortened it ):
SELECT server.dbo.SRFILE.SR_GROUP, server.dbo.SRFILE.SROWNERSHIP, server.dbo.SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,
BSFLBWF_1.Severityx FROM server.dbo.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = server.dbo.SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL).........
I am using a GridView and a TemplateColumn in GridView, Now on selction of checkBox (header Template) I need to run server side function. When I am trying using chkSelectAll_CheckedChanged thenit is not executing also I am calling client side code on click of checkBox (header Template), which is used to select all checkbox in item template.Code Part of GridView (.aspx)
<asp:TemplateField HeaderText="Select">
<ItemTemplate>
<asp:CheckBox ID="chkDomain" runat="server" AutoPostBack="true" OnCheckedChanged="chkDomain_CheckedChanged" />
[code]...
I have one Store Procedure that generating report ..
For storing the data there , i used many temp. tables. There is many Select Query , little less insert and delete query.
Now if there is huge data around 1 lac in temp table my select query taking to much time and also may be insert and delete query
I added Primary key to all auto Increment Field in temp table. Also defined Clustered index on that primary key as unique Clustered index to improve the performance .
But there is not so much improvement in case of huge temp table.
Right now the whole Store Procedure is taking time to complete around 1.5 days or around 30 hours ..
So i want to increase the performance as much that it completes on nearly 3-4 hours.
I'm implementing a BLL layer and have a few questions. First, in my DAL, there is a contacts table, the select method is a sql query against a sql server 2000 database. The preview data returns everyting as expected. The query is
[Code]....
The BLL method is built like so
[Code]....
When using the BLL method to a gridview, the results of the subquery are not displayed. There are also other methods, each having a different where clause for ContactRole_ID and each is having the same issue of not displaying the subquery reult in the gridview. No error, but no data is displayed for the subquery either. Here is the templatefield of the gridview
[Code]....
Another item that is causing an error is in trying to update a record. Here is the DAL update method
[Code]....
And the BLL update method
[Code]....
The error says i have extra parameters, but have no clue where the extra parameters are being picked up. The error isObjectDataSource 'odsContacts' could not find a non-generic method 'UpdateContact' that has parameters: _ContactID, _Active, _ContactName, _Office, _VirtualOffice, _Pager, _Fax, _Home, _Cellular, _Email, _ContactRole_ID, Active, ContactRole_ID, ContactName, Office, Email, VirtualOffice, Pager, FAX, Home, Cellular.And the objectdatasource
[Code]....
This is something I can easily do in SQL, but I'm pretty new to LINQ and haven't been able to find an answer online.
I need a LINQ query that returns two values; the first a distinct value and the second the count of the first value. This is filtered by a parameter. Here is my table:
[code]....
