|
Thank you Dxlee, not only that it works, I learned a new way of using inner join table! I used to think inner join is only for joining two different tables, but now I see that you can use it to link to the same table filtered for some criterias. This is going to change the way I write queries from now on
Could you please recommend a good online source where I can learn how to use SQL more effectively? Using INNER JOIN in this manner (seems creative and ingenious to me) was not covered in W3Schools nor in most SQL tutorial websites. Many thanks again!
|
|
|
|
|
I think d@nish's solution is better for your case, even though you found my inner join to be new and helpful to you.
I am sorry I don't have a book or website to recommend to you on SQL. I learned something about SQL by reading an Oracle manual on SQL*Plus (which has lots of very interesting examples) about 20 years ago (in 1990) when I was working on a project for a friend. I moved a few times (to different countries) ever since and lost that manual. It was for an old version of Oracle anyway (which ran on VMS with no graphical user interface.)
The new Oracle manuals seem to have nothing similar to the one I read. Your best bet is to find a good book on SQL.
|
|
|
|
|
You must be having multiple prices for that date. If you need the top record only, use order by and top 1 in you statement.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thank you D@nish for the tip, yes indeed there are multiple prices on same dates belonging to a ticker, though that shouldn't happen. Bad data, may need to redownload.
Top 1 solves this for now. Many thanks again.
|
|
|
|
|
You can always you TOP 1.
SELECT TOP 1 mycolumn FROM mytable
This probably will not work for your case but I thought I would throw it in.
|
|
|
|
|
Thank you this also works djj55
|
|
|
|
|
Hi,
What is the query to get the list of table names for MS Access.
In Sql Server :
USE [MyDatabase]
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
In MS Access ?
Thanks in advance.
|
|
|
|
|
SELECT
Table_Name = Name,
Row_Count = DCount("*",[MSysObjects].[Name])
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
ORDER BY
Name
Source can be found here.
I are Troll
|
|
|
|
|
Hi Eddy,
Thanks for your response.
I got one more alternative for that...
OleDbConnection con = new OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source="+Server.MapPath("~/App_Data/Sample.mdb"));
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
con.Close();
|
|
|
|
|
sekhar.k wrote: I got one more alternative for that...
The .NET version would be preferable over an SQL-statement; it's obvious what the code does if you read your version. It's not that obvious what the query on the MSys tables does.
Well done
I are Troll
|
|
|
|
|
Hi All, I am trying to create a report for every hour based on a selected day.
I am trying to use sql statement. For example, I have dateTimePicker and I select a dy of a month
then it should get all the data that took place from 12am to 11pm for every hour.
FYI : my database sqlite. coding in C#
thanks for any help in advance.
|
|
|
|
|
And what is the problem?
|
|
|
|
|
Thanks Dmitry, I am not an expert in sql. therefore, I don't know how to do that.
FYI: TableOne has Column Sell, quantity, emID,orderDate,shipDate.
I want to know how many sell was made on a given day. And how many sell was made per hour in a gaven day(24 hours).
|
|
|
|
|
You should create a query with condition, like this:
select count(*) from TaleOne where shipDate between <start_date> and <end_date>
You can google and find how to use queries in sqlite, there are plenty of such info.
|
|
|
|
|
SELECT COUNT(SELL)
FROM TableOne
GROUP BY CONVERT(DATETIME, orderDate, 101)
The convert is to use short date
For the hour look at DatePart[^]
It is always good to read a book or tutorials before jumping to development.
|
|
|
|
|
SQLite doesn't have DatePart ; you should save dates as long in sqlite.
|
|
|
|
|
Thanks all for your help. I will try to do this at the same time I am going to read books on sql
thanks.
|
|
|
|
|
My bad - missed that fact that its a SQlLite question. Nice article by the way.
|
|
|
|
|
Hy to All;
I want to use two insert statement in one stored procedure
I have Two Tables "Activitity" (ActivitityId as a primery key)
and "TODo" Second Table (TODOId as a primery) and (ActivitityId as a foriegn key) ....
So when a value insert into "Activitity" Table ,I want to insert its primery key into "TODO" Table as Foriegn key in a single Stored procedure...
So for this purpose I want to use @@indentity variable
how can I use @@indentity variable in my stored procedure....
|
|
|
|
|
if you have a column set as identity you can use SCOPE_IDENTITY()
DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()
|
|
|
|
|
So store the @@Identity or Scope_Identity() in a variable directly after inserting the activity record and use it in the second insert for todo, there you have your relationship
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even you can go ahead with OUTPUT.INSERTED
e.g.
insert into Activitity
output inserted.ActivitityId into TODo(ActivitityId)
values(1,'somerecord')
select * from TODo
Output:
TODOId ActivitityId
1 1
Niladri Biswas
|
|
|
|
|
Now that's cool, I was not aware INSERTED worked for an ordinary insert statement, I thought it was only for triggers. Now to go play, I wonder is UPDATED works as well
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi guys,
I have created a trigger for auditing ..
I want to keep track of changes made to each field by using a counter.
can you let me I go about achieving this task....
modified on Wednesday, January 13, 2010 5:34 AM
|
|
|
|