February 27, 2015 at 7:02 am
Hi, I have a question regarding SQL. I am writing a program in which I am getting desired data with two queries but it is taking too much time, but I am unable to combine these queries.
First Query
select ais_data.mmsi, MAX(ais_data.mssisdtg1) from ais_data where ais_data.dtg=\'" + dtg + "\' and (ais_data.message=1 OR ais_data.message=2 OR ais_data.message=3) group by ais_data.mmsi
In this query I am providing date to execute it and this query is providing me max(mssisdtg1) with mmsi on the basis of grouping mmsi because mmsi are duplicate here. Then this query supposedly providing me 1000 record. Then on the basis of this query I am executing another query in the loop.
Second Query
select ships.name, ais_data.mmsi, ships.country,ais_data.course,ais_data.sog,ais_data.heading,ais_data.latitude,ais_data.longitude from ships LEFT JOIN ais_data ON ships.mmsi=ais_data.mmsi where ais_data.mmsi="+mmsi+" and ais_data.mssisdtg1=\'" + mssisdtg + "\'"
I want to combine these two queries. Because it is taking two much time to execute.
So I can execute it in one loop. Thanks
February 27, 2015 at 7:19 am
it looks like some inline strings/code that is being constructed in c#, which is then being executed, presumbly by LoadDatatable?
i'm not sure where =\'" + dtg + "\' is coming from, but combining the two queries i think would look something like this:
DECLARE @dtg varchar(10) --?=\'" + dtg + "\' ? what datatype?
select
ships.name,
ais_data.mmsi,
ships.country,
ais_data.course,
ais_data.sog,
ais_data.heading,
ais_data.latitude,
ais_data.longitude
from ships
LEFT JOIN ais_data
ON ships.mmsi=ais_data.mmsi
INNER JOIN (select
ais_data.mmsi,
MAX(ais_data.mssisdtg1)
from ais_data
where ais_data.dtg=@dtg
and ais_data.message BETWEEN 1 AND 3
group by ais_data.mmsi
)MyAlias
ON ais_data.mmsi=MyAlias.mmsi
and ais_data.mssisdtg1=MyAlias.mssisdtg1
Lowell
February 27, 2015 at 12:51 pm
Sir, Thanks for your advice!!!
Basically dtg is coming from a web form. I hard coded the dtg here. I applied the query, but it is giving me some error. I am not very expert in databases thats why I am asking for help. I will act upon your advice and one day I will also be able to help others.:-)
select
ships.name,
ais_data.mmsi,
ships.country,
ais_data.course,
ais_data.sog,
ais_data.heading,
ais_data.latitude,
ais_data.longitude
from ships LEFT JOIN ais_data ON ships.mmsi=ais_data.mmsi
INNER JOIN (select ais_data.mmsi,MAX(ais_data.mssisdtg1) from ais_data where ais_data.dtg='2015-02-27'
and ais_data.message BETWEEN 1 AND 3 group by ais_data.mmsi) MyAlias
ON ais_data.mmsi=MyAlias.mmsi
and ais_data.mssisdtg1=MyAlias.mssisdtg1
I think last two lines are giving following errors:
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'MyAlias'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'mssisdtg1'.
February 27, 2015 at 1:54 pm
i forgot the alias for the max:
MAX(ais_data.mssisdtg1) AS mssisdtg1
select
ships.name,
ais_data.mmsi,
ships.country,
ais_data.course,
ais_data.sog,
ais_data.heading,
ais_data.latitude,
ais_data.longitude
from ships LEFT JOIN ais_data ON ships.mmsi=ais_data.mmsi
INNER JOIN (select ais_data.mmsi,MAX(ais_data.mssisdtg1) AS mssisdtg1 from ais_data where ais_data.dtg='2015-02-27'
and ais_data.message BETWEEN 1 AND 3 group by ais_data.mmsi) MyAlias
ON ais_data.mmsi=MyAlias.mmsi
and ais_data.mssisdtg1=MyAlias.mssisdtg1
Lowell
February 27, 2015 at 9:14 pm
Thanks!!!:-)
February 27, 2015 at 9:32 pm
One more little information is required.
If we want to restrict some data in first portion of a query.
For example where ships.country='Malta'. Where we will do that?
February 27, 2015 at 9:35 pm
Yes done. Its done!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply