|
if i want to display only last entered 4 records from a table, then which query will be written
|
|
|
|
|
mavii wrote: if i want to display only last entered 4 records from a table, then which query will be written
Well, The table needs a date stamp (DateTime column) that records when data was inserted.
SELECT TOP 4 * FROM MyTable<br />
ORDER BY DateStamp DESC
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
SELECT TOP 4 [fields] FROM [table] ORDER BY DateStamp DESC
SSK.
|
|
|
|
|
its very urgent and need a code
i have to traverse a values in a table of database one at a time of almost 19 digits in string format which is need to retrive from a specific column of a sheet of MS-Excel and need to match with all values in a specific field of that table.
Hussein
|
|
|
|
|
Connect to Excel using ADO (System.Data.Oledb classes for .NET) with the following connection string:
e.g open an OledbConnection using:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= \myfilepath\myexcelsheet.xls
;Extended Properties=\"text;HDR=Yes;FMT=Delimited;\";Extended Properties=\"Excel 8.0;\""
Note that HDR =Yes implies the first row in the Excel sheet is to be treated as column names.
If you use HDR=No, the first row is data, and the sheet column names (A,B,C,D...) are used.
Open the worksheet (in a DataReader or a DataAdapter) using a query like this one:
"select my19digitfild from $MyWorksheetName where mymatchfield = mymatchvalue"
Note the $ in $MyWorkHeetName. The $ indicates the "table" name to use is the worksheet tab name (e.g $Sheet1). If you omit the $, it will look for a named rage top determine the 'table'.
Good luck.
|
|
|
|
|
hi all
i want to download mysql free version to connect it vc++
please tell me a direct link as i need it
i try but not succeeded
thanks ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
hi
|
|
|
|
|
|
after a spacific search criteria (sql statement )
the results was some names
required :
i want to edit all this records with another name
plz help me
|
|
|
|
|
In your select statement is where you would change column names.
So if your table looks like this:
Select Firstname,
LastName,
account,
PhoneNumber from yourtable
you can change the column names like so:
Select FirstName as First,
LastName as Last,
account as Acct,
PhoneNumber as Phone
from yourtable
Hope that helps.
Ben
|
|
|
|
|
no that's not what i mean
but thnaks 4 ur reply
i mean when i made this statement he gave me a coloum called fatrname
this coloum contain some names like this : maged1
maged2
maged3
all what i mean is if there is any way to type insetad of all these names one word as "maged" only or whatever
in another words i want to integrate all these (maged1,maged2, .... )to the word that i want which is "maged"
|
|
|
|
|
Ok it sounds like you have three columns in a table and you want to return only one column. If all three columns are of the same type you can do a union. So if you have:
Select maged1,
maged2,
maged3 from yourtable
You can do
select maged1 as maged from yourtable
union
select maged2 as maged from yourtable
union
select maged3 as maged from yourtable
Ben
|
|
|
|
|
sorry to tell u that's wrong too
i have table here contain some itmes
after search he gave me this results
maged1 maged2 maged3 ....
and here is my sql statement :select fatrname from reglz where fatrname LIKE '%" & TextBox1.Text & "%'
after that i want to edit them to only the word maged
|
|
|
|
|
You want to issue an update statement, like this:
"update reglz<br />
set fatrname = 'maged'<br />
where fatrname like '%" + TextBox1.Text + "%'" Now, before you go getting all excited about this there are a couple of things you should be aware of.
1. This code is extremely prone to SQL Injection attacks, so I would suggest that you would want to look at changing this to use parameters (without knowing which database you are using, I can't really tell you which parameters to use). (See this[^] article on SQL Injection attacks. Colin does a great job of explaining what it's all about.
2. You should never rely on the default names of properties. Please change TextBox1 to be something meaningful.
|
|
|
|
|
your query is not clear.
is that you want to use column alias for your insert/update/....
Regards
KP
|
|
|
|
|
Please read the forum guidelines. Do not use words like 'urgent' or 'help' in your message titles. Give them some meaning.
Also, do not use text speak.
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
I would like to see a function that tells people that their message couldn't be posted because text speak is in the message. Wouldn't be that hard to validate the subject line...
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
In Brinkster.com free hosting, I think if you violate hosting rules like setting more ScriptTimeout, your ASP would be renamed as .BADTIMEOUT by an ISAPI filter.
How about having such a ISAPI filter that does the following tasks:
(*) Append 'VIOLATING_POST'
(*) Auto Vote (1) from CP Server itself. 
|
|
|
|
|
Please use an appropriate subject.
magedhv wrote: urgent plz
This does not convey anything.
magedhv wrote: spacific
Check your spelling (typo). I was interpreting it as South Pacific. 
|
|
|
|
|
Hi there,
I have an Access App with a SQL Server 2000 back end. When I Modify a table, then refresh the table in Access I get a 'Write Conflict' error message, even though I'm the only user logged on.
This happens when I refresh the linked table in Access and also when I delete the table in Access, and then refresh.
It only happens to existing rows, not new ones, and I've created a new table based on the current one, but still get the same problem.
I don't get the problem if I change a row in SQL Server.
I've also taken the mdb offline and online again, hoping this would reset the link. It still doesn't work.
Any ideas?
Thanks in advance
Jon
|
|
|
|
|
Solution - a new column was a bit data type : all existing rows require a value of 0 / 1 in that column.
|
|
|
|
|
hi guys
i am using oracel server , and vb.net,
in my database i have a table named abc in this table
i have a date field which contains date values and its type is date
in the format "10/13/1995"
there are rows in this filed for the same day as well also,
means it may also contain another entry like above "10/13/1995"
the issue is that i want to get dates based on months (group by) but based on
a particular year,
means i want it should show me all the group by rows for each year and for each month
like
1995= year , 07= month, 3 (number or rows)
1995= year , 06= month, 6 (number or rows)
1996= year , 07= month, 3 (number or rows)
1996= year , 06= month, 6 (number or rows)
hello
|
|
|
|
|
You should be able to do something like:
select to_char(MyDateField, 'YYYY'), to_char(MyDateField, 'MM'), count(*)
from MyTable
group by to_char(MyDateField, 'YYYY'), to_char(MyDateField, 'MM')
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi all,
I am having a database in SQl server 2000.In this database some tables are having around 8 lakhs records, when i am fetching the records from these tables it is taking more time (some times more than 10 seconds). Each table is have indexe.Can any body tell me How to reduce this fetching time ? or should i rearrange the indexes ?
Thanks,
S.Raju.
|
|
|
|
|
|
Without knowing anything about the schema, the data, the query, or what indexes you already have, it's hard to help.
General performance testing: before testing the performance of any query, dump the data in RAM so that you can get a true worst-case (all data coming from disk). You do this by running DBCC DROPCLEANBUFFERS. If you don't, a query may appear to be fast but a large amount of CPU usage occurs - this is the processor having to read a lot of different areas of memory and wait for that data to be read into its caches from slow main memory. It's often also worth making SQL Server recompile your query: run DBCC FREEPROCCACHE.
Then you need to look at what SQL Server is actually doing with your query. In Query Analyzer (you can't do this from Enterprise Manager), select Query, Show Execution Plan, then run your query.
SQL Server has five basic ways of accessing data in a table, grouped into two basic strategies. To understand these, it helps if you understand how SQL Server organises indexes. Basically the index is just a list, in sorted order, of all the values in the index columns, one entry per row in the table, plus a bookmark to find the actual row in the table. In a Clustered Index, the actual data for the remaining columns is present instead of the bookmark (and this is the only place that the data for the table is stored). For a non-clustered index on a table with a clustered index, the bookmark is actually a copy of the key columns in the clustered index. Otherwise it is just a pointer to the record in the table.
The two strategies are Seek and Scan. A Seek means performing a binary search to locate the first record in the index matching a given query, then examining each row in turn until a non-matching value is found, at which point it stops (because the index is sorted, it knows there are no more matches beyond this point). A Scan starts at the first record and simply compares each entry (in the actual row itself, or in the index) to the value desired (if another query is already scanning the same table or index, SQL Server Enterprise Edition can start from the point that the other query has reached to reduce memory thrashing). Seek is generally much, much faster than a Scan operation. In the worst case, a Scan must read every record.
Because of the nature of the Seek, it can only be performed on an index, while a Scan can be performed on a clustered or non-clustered index or on the table itself. So the five operators are Table Scan, Clustered Index Scan, Index Scan, Clustered Index Seek, Index Seek. For non-clustered index seeks or scans you will probably see a Bookmark Lookup too. However, if the non-clustered index contains all the data that you're actually requesting, SQL Server does not need to refer to the base table or clustered index, and the Bookmark Lookup does not occur. In this case the index is referred to as a 'covering index' for the query. You should avoid SELECT * for this reason. Only ask for the columns you need.
The ideal, to me, is to have a covering index for each query, so the server can rapidly locate the data. The cost of building and maintaining such indexes can be prohibitive on a table that is updated regularly.
Note that SQL Server will rarely use multiple indexes on the same table to locate data. The cost of comparing the results from more than one index is high, and normally the query optimizer will conclude that using one then filtering the results will perform better than reading multiple indexes.
Seeks are most effective when the indexes are highly selective - that is, there is a reasonable distribution of a lot of different values. If a column can only have two values, and those values are evenly distributed, there actually isn't a lot of point using an index on that column as the bookmark lookup cost becomes quite high - generally SQL Server then uses a different index that is more selective, or performs a scan instead. If instead most of the rows have value A with only a few having the other value B, the index might be helpful when looking for rows with the B value. This can be problematic as SQL Server compiles queries based on the values given the first time, which may not be suitable if different values are given.
Finally, to use a Seek, the query must form a search argument or sarg - Microsoft refer to it as sargable. See here[^] for a discussion of what is and is not sargable.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
