T-SQL (SS2K8) :: Indexing Tips For MIN And MAX In Queries
Jun 6, 2014
Are there any best practices for indexing to support queries with MIN() and MAX() in them? what if MIN() and MAX() are partitioned? Super bonus question: what if MIN() and MAX() are not only partitioned, but are called on a field in a derived table, and one of the partitioning elements comes from a table that's being joined in the derived table?
I experimented with inserting the derived table into a temp table, putting a POC index on that, and querying out, but that actually took longer.
View 9 Replies
ADVERTISEMENT
May 9, 2008
Hi.
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
Thanks,
Prasad
View 1 Replies
View Related
Dec 1, 2005
Hi everybody,After days reading stuff about indexing, extents, pages, 8KB, 64 KB,IGNORE_DUP_KEY, CREATE INDEX, bla bla, lalalala, lllllll, grrrrrrr andtesting with different kinds of queries, indexes, situations i'mgetting confused, irritated, etc.SITUATIONI have the following situation. We have a fact table with 1,8 millionrecords (Testsituation) and i'm inserting 100000 records and i want tomake it faster. Records can only be inserted when it's different fromthe one in the Fact table. Currently we don't have any index on thetable. So i thought that could be quicker when i build some indexes. Soi started experimenting, lalala, and some further more and more...The facttable has Foreign keys and measures. The foreign keys arereferenced with the primary keys in the dimensions. Also i have a fieldcalled Source_key which indicates an unique value (This could be aninteger, but also a varchar). In this case its a varchar. Also i havean Checksum field. An example of the query is like this (based on thenorthwind database):INSERT INTO DYN.dbo.SAW_Fact_Order_DetailSELECT 20051130, 62667,Customer_Dim_Key =ISNULL(DYN.dbo.SAW_B_LU_Customer.Customer_Dim_Key, 0),Product_Dim_Key = ISNULL(DYN.dbo.SAW_B_LU_Product.Product_Dim_Key,0) ,FK_Order_Date,FK_Required_Date,FK_Shipped_Date,Shipped_Flag,Order_Duration_Open_Days,Order_Required_Exceed_Days,Order_Detail_Unit_Price,Order_Detail_Quantity,Order_Detail_Discount,ExtendedPrice,Order_Number,Order_Detail_Number,Order_Detail_Count,binary_checksum(ISNULL(DYN.dbo.SAW_B_LU_Customer.Customer_Dim_Key, 0),ISNULL(DYN.dbo.SAW_B_LU_Product.Product_Dim_Key,0) ,DYN.dbo.TR_Fact_Order_Detail_V.FK_Order_Date,DYN.dbo.TR_Fact_Order_Detail_V.FK_Required_Date,DYN.dbo.TR_Fact_Order_Detail_V.FK_Shipped_Date,DYN.dbo.TR_Fact_Order_Detail_V.Shipped_Flag,DYN.dbo.TR_Fact_Order_Detail_V.Order_Duration_Open _Days,DYN.dbo.TR_Fact_Order_Detail_V.Order_Required_Exce ed_Days,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Unit_P rice,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Quanti ty,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Discou nt,DYN.dbo.TR_Fact_Order_Detail_V.ExtendedPrice,DYN.dbo.TR_Fact_Order_Detail_V.Order_Number,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Number ,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Count) ,14,'N',getdate(),'1/1/2049'FROM DYN.dbo.TR_Fact_Order_Detail_VLEFT OUTER JOIN DYN.dbo.SAW_B_LU_Customer ONDYN.dbo.TR_Fact_Order_Detail_V.Customer_Code =DYN.dbo.SAW_B_LU_Customer.Customer_CodeLEFT OUTER JOIN DYN.dbo.SAW_B_LU_Product ONDYN.dbo.TR_Fact_Order_Detail_V.Product_Code =DYN.dbo.SAW_B_LU_Product.Product_CodeWHERE NOT EXISTS(Select *From DYN.dbo.SAW_Fact_Order_DetailWhere Checksum_Field = binary_checksum(ISNULL(DYN.dbo.SAW_B_LU_Customer.Customer_Dim_Key, 0),ISNULL(DYN.dbo.SAW_B_LU_Product.Product_Dim_Key,0) ,DYN.dbo.TR_Fact_Order_Detail_V.FK_Order_Date,DYN.dbo.TR_Fact_Order_Detail_V.FK_Required_Date,DYN.dbo.TR_Fact_Order_Detail_V.FK_Shipped_Date,DYN.dbo.TR_Fact_Order_Detail_V.Shipped_Flag,DYN.dbo.TR_Fact_Order_Detail_V.Order_Duration_Open _Days,DYN.dbo.TR_Fact_Order_Detail_V.Order_Required_Exce ed_Days,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Unit_P rice,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Quanti ty,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Discou nt,DYN.dbo.TR_Fact_Order_Detail_V.ExtendedPrice,DYN.dbo.TR_Fact_Order_Detail_V.Order_Number,DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Number ,AND DYN.dbo.TR_Fact_Order_Detail_V.Order_Number =DYN.dbo.SAW_Fact_Order_Detail.Order_NumberAND DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Number =DYN.dbo.SAW_Fact_Order_Detail.Order_Detail_NumberAND Expired_Date = '1/1/2049')EXPERIMENTSSo i focused on the NOT Exists clause. My thought was that when anindex is created on the fields in WHERE clause of the SELECT statementin the NOT EXISTS part it would be quicker. Because SQL Server shouldbe quicker decisioning whether a record existed or not. So far theory.So i started experimenting.I No Index.It took about 118 seconds.II. I created an referencetable (took some time but not relevant here)so the insert table and the comparetable were not the same anymore. Thefields in the reference table are Checksumfield, Dossier_Source_Code(in query example Order_Number and Order_Detail_Number) andexpired_date. So the not exists clause rebuild to reference this newlycreated table and now it took about 85 seconds.III. The part i don't understand is this. So the prior step was aperfomance gain so i decided to build indexes on the reference table. Itried al types of different indexes:* index on checksum (clustered), source_key (Non clustered, unique) andon expired date (non clustered) --> 99 seconds* Index on source_key (clustered, unique), checksum_field (nonclustered) and on expired date (non clustered) --> 91 seconds* the Source key (in the example Order_Number and Order_Detail_Number)was unique so i decided to build a checksum on these fields and buildan index on the checksum and of course on the Source_key andexpired_date. This took about 101 seconds. What?So as you can see it took only longer when i build a index. So why? Andhas someone any clues to make the query faster?Thanx ,Hennie
View 6 Replies
View Related
Aug 12, 2006
I've been driving myself nuts trying to get a sensible product search
going. The existing live site search is just a LIKE %searchterm% on
the Title field in our Products table. Fast, but not great ;) Talks
between IT and Marketing have resulted in this being the desired
results and order:
Exact Title match
Substring Title match
Substring Keywords match
Substring Description match
OK, I can easily do this with UNION queries using LIKE (and a virtual
"weight" column in each query), but the query takes too long. So I'm
trying out SQL Server full text indexing, in an attempt to get the
speed up (and the natural language stuff is just plain cool).
Where I'm running into problems is doing a FULLTEXT match on
Description. It seems that to do a phrase match (e.g. "new york"
should match only Descriptions where the phrase "new york" occurs) I
need to enclose the search term in quotation marks in the query (or
maybe single AND double quotes).
But using parameters in ASP.NET (which I'm supposed to do to avoid SQL
injection attacks, yes?) I don't really have full control of the
quoting - ASP.NET and/or SQL Server automagically quotes strings for
me before passing them into the query. I think.
For example, this doesn't find any description matches:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)
set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"
SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"@theSearchTerm"'))
ORDER BY theWeight DESC, m.ShortName
==========================================
But this one (where I put in the actual string instead of using the
parameter) *does* get the desired results, including matches in the
Description:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)
set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"
SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"new york"'))
ORDER BY theWeight DESC, m.ShortName
==========================================
Trying various permutations of quotes around the parameter gives me
either syntax errors or undesirable results.
Has anybody tried this sort of thing? How did you do it?
Thanks,
Greg Holmes
View 4 Replies
View Related
Feb 9, 2015
I am trying to insert the queries in a table such as the following, not able to insert it.
create table test (txt Varchar(200))
insert into test values('select *from master.dbo.sysprocesses WHERE status NOT IN('sleeping','background')')
View 4 Replies
View Related
Nov 16, 2014
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
View 9 Replies
View Related
Sep 22, 2006
Hi
I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?
View 14 Replies
View Related
Sep 10, 2007
Hi,
The other day we tried online re-indexing feature of SQL 2005 and it€™s performing faster than offline re-indexing. Could you please validate if it€™s supposed to do be this way? I always thought offline should be faster than online.
Thanks,
Ritesh
View 5 Replies
View Related
Aug 4, 2007
Employee Date Enquired Title Company
Bryan Cole 2007-05-01 Recruiter ABC
Bryan Cole 2007-05-15 Recruiter ABC
Bryan Cole 2007-05-21 Recruiter ABC
Bryan Cole 2007-06-15 Recruiter ABC
Bryan Cole 2007-07-01 Recruiter ABC
Bryan Cole 2007-07-30 Recruiter ABC
I have to do row by row date comparisons in a date column. If the date difference is more than 30 days we keep it , otherwise we suppress it. How can we write the query without using cursor so that only the bold rows will come ?
View 9 Replies
View Related
Nov 7, 2000
What is it mean GHOST in SQL Trace column ?
Thanx in advance.
View 1 Replies
View Related
Jun 13, 2008
Hi Gurus ..
Could any one create a thread called Tips for the Day and Give any imports and complex query to learn on each and every Day ?
Thanks in Advance
Dana
View 7 Replies
View Related
Dec 20, 2006
Dear all,I'm designing a system including the database and the securityrepresents the most crucial aspect of the system; hence for thedatabase security i have implemented the following aspects and needyour advise on further aspects or perhaps corrections where by thesystem is web based using asp.net and under iis 6.0 with https; in theasp.net engine side, i have included client-side validations for whatever is inputed and validated against sql injections for postbackforms:The features of security in SQL Server 2005 side i have implemented:1.) Created MACHINEASPNET Account2.) Allowed ASPNET Account to access the DB3.) Explicity denied ASPNET Account all permissions to all tables,functions and views4.) Denied all permissions to the ASPNET user for stored proceduresexcept EXECUTE permissions5.) By Stored procedure creation, WITH ENCRYPTION, EXECUTE AS'MACHINEASPNET' was usedNo SQL was included in the asp.net code except for calling storedproccedures; the policy is to only call stored procedures within theasp.net pages and encrypt the connection strings inside the web.configfile.Kindly, give me some guidelines for better security or discuss with methe security aspects i mentionedRegards
View 1 Replies
View Related
Feb 29, 2008
hi,
pls help me ,i am going to attend the interview for SQL DBA pls give me a some tips.
View 1 Replies
View Related
Mar 22, 2004
Somebody can give some tips or hints for speed up my querys and procedures from sql and get more performance, what is best to use, joins o cursors, using cursors can give me more performance?
View 1 Replies
View Related
Jan 6, 1999
Hi all:
I'm new to replication but I have already set up replication and have seen it
working and failing and have gotten myself out of jams so far but there must
be an easy way to administer it when things don't replicate as expected. I'm
finding that I could easily kill half a day just trying to dig up
information leading to troubleshooting tips. Is there documentation just on
managing this feature. The regular MS Administrator's guide doesn't offer much.
Currently I have a problem that if replication fails on one command I get a
SQL Mail telling me of the problem but does replication continue to the next
command or does it just stop until the problem is fixed? I'm finding that I am
constantly checking the publisher and subscriber databases and verifying if
replication is indeed doing what the msjob_commands table reports. I set the
batch to commit after each transaction instead of every 100.
Help...
View 1 Replies
View Related
Sep 4, 2007
Hi,
For your day-to-day SQL Server issues like query tuning, optimization, TSQL problems, I am writing the blog called
http://blog.namwarrizvi.com
Some of the latest articles are:
Generating 1 million rows in less than a second
Conditionally add column in the table
Multiple Inserts in one statement
Capture every data operation in SQL Server 2008
100 Nano seconds precision in SQL Server 2008
Represent Trees and Graphs in TSQL
MERGE Statement of SQL Seerver 2008
Return Last n Orders by using APPLY operator
Number padding in TSQL
Microsoft Performance Point Server and Sharepoint
Caching and Recompilation in SQL Server 2005
and many more....
I will really appreciate comments and suggestions.
View 5 Replies
View Related
Nov 26, 2007
Hello,
We come accross situations where people are running big updates on the database (i.e. 50.000 updates).
Our problem is that those big updates are blocking other user updates.
Thanks to snapshot isolation, users can query (select) the database with no lock.
We rebuilt the indexes setting that the indexes used by the update procedure would not use page locks and only row locks.
We set that the database would update the statistics asynchronously.
Now we are still facing blockings and we would like to optimize the database to avoid those blockings.
What else could we check? Any tips regarding the way to avoid that problem is really welcome.
Thanks,
Olivier
View 9 Replies
View Related
Jul 23, 2005
Hello I have 2 sql servers in my company and many remote sites. I amtrying to figure out the best way to keep them safe, since both haveaccess to the internet behind the firewall. I was planning to disbablethe default gateway on one or maybe disabling file sharing on both, iwas also thinking to block access to the the terminal server that isrunning in admin mode, either through the firewall or the permissionsof the rdp protocol. I have a few admins that have account manager andserver operator permission as well as exchange admin. Any ideas in howto restrict access to my servers? thanks.
View 1 Replies
View Related
Jun 9, 2008
Hi everybody,
i would be thankful if anybody posts some references of the web sites
which relates to SQL Server Performance tuning.
Thanks in Advance
Suresh Kumar Goudampally
View 2 Replies
View Related
Aug 27, 2004
I've created a few indexes on my tables but before I over-do it I wanted to see if there were any good websites out there with recommendations. My plan is to create clustered indexes on my primary keys and non-clustered for each foreign key. Also there are a few fields that are regularly searched so I will index them separately as well.
I don't want too many though because I know that affects the performance of record inserts. I'm also not sure about using multiple keys in one index.
Is there a good site out there with tips on what indexes to create or avoid, or have I pretty much covered it?
View 1 Replies
View Related
Jan 6, 2004
Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated)
-------------------------------------------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
-------------------------------------------------
end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
Thanks for any time spent!
View 14 Replies
View Related
Apr 1, 2004
please post your idea, experiences what is fastest way to maniulate ms sql server using vb6 .
thanks...
View 12 Replies
View Related
Dec 1, 2005
We are coming out of the dark ages with our app using SQL 7 and, following the excellent advice of the folks here on SQLTeam, installing SQL 2005 Express on our new webserver.
Not being terribly fluent in all things SQL, I was wondering if anybody could provide input on the best practices for getting SQL 2005 Express going on the new server.
So far I've:
- Installed SQL 2005 Express
- Downloaded and "installed" the SSEUtil for CMD line instructions
- Downloaded and installed the graphical management interface (very nice, makes me feel more comfortable - like SQL 7 console!)
- Copied backup files (made using SQL backup maintenance) to the new server
Should I simply create an empty db of the same name on the 2005 server and then restore the 7 data? Or ????
I searched briefly for previous posts of this nature and didn't find too much info so I hope I'm not duplicating effort here...
Thanks in advance for any advice!
Mmmmmkay. Yeah, did you get the memo about the TPS reports?
View 3 Replies
View Related
Oct 9, 2007
I need to convert an excel matrix into a table. Currently, the data consists of months going across the top and business names going down the left side. Each business name has three rows of data per monthly column, such that there are three numbers in the january column, three in the february column, etc. etc.
I want to convert to a table that has five columns, the business name, date, and the three data columns.
Any help would be greatly appreciated. As of right now I'm staring at keying in about 2000 rows of data by hand.
View 4 Replies
View Related
Mar 8, 2007
Our parts table has 5k records. I want to use part number as a parameter for one of my reports. Is there a way to do this and have the report generate in a reasonable amount of time?
Thanks
View 3 Replies
View Related
Jan 25, 2008
Hi, All experts here,
Thank you for your kind attention.
I have questions about Slowly Changing Dimensions. I am quite confused about when should we use type 1 ( changing), type2 (historical), or type3( fixed) for the dimensions in each table? Is there any good suggestions on that?
Thank you in advance and I am looking forward to hearing from you.
Yours sincerely,
View 1 Replies
View Related
Jan 18, 2008
Hi,
I have a SSIS package that reads a text file and generates an output file out of it after transformations.
Now, a 20MB text file (containing about 50,000 records) is taking around 5 mins to complete.
There is a Data Flow Task which is taking the major chunk of the time.
It contains the following:
1) File Source
2) Conditional Splits (2 in number)
3) Derived Column
4) Data Conversion Transformation
5) OLE DB Destinations (3 in number)
The number of records being processed is close to 50,000
Please share your tips for optimizing the package.
Thanks in advance.
Regards,
B@ns
View 4 Replies
View Related
Dec 16, 2001
Originally posted by Jeremy at 12/10/2001 11:39:38 AM
Hello all,
I've written a simple dts job that uses oracle (8.x) as a source and oracle (8.x) as a destination. I'm using SQL 2000 and Microsoft's oledb provider for oracle as the two connections. I've chosen "Transform Data Task" with the following SQL "SELECT * FROM REPORTER_STATUS
WHERE LASTOCCURRENCE > TRUNC(SYSDATE)". As you can see, it's very simple, however it's very very very slow. (averages about 1000 rows per minute). In my column transformations, I've selected many to many versus the one to one. There are no activex scripts or anything along those lines. Just a simple push of the data from one oracle box to the other. The table schemas are identical etc... I've had this problem before with writing to Oracle and I can't imagine that it's really supposed to be this slow. If you need more details, please just let me know.
Thank you,
Jeremy
-----------------------------More -------------------------------
The official response from microsoft is that dts only allows for single inserts... not bulk or bcp for oracle. There must be someone out there who has figured out how to configure / modify / call (something) from a dts pacakage to insert millions of records into Oracle in a decent time frame...
thnx again..
View 2 Replies
View Related
Feb 10, 2008
Hi all..!!
this is my first posting in this forum and I registered since it is very informative and active...
I am looking for pointers to go ahead with migration of DB/DTS from 2000 to 2005.
Any gurus in this forum who can point me any existing links or any info will be highly appreciated..
Thanks
View 1 Replies
View Related
Jul 20, 2005
I have a stored procedure that queries a database using a Selectstatement with some inner joins and conditions. With over 9 millionrecords it takes 1 min 36 sec to complete. This is too slow for myrequirements.Is there any way I can optimize this query. I have thought aboutusing an indexed view. I haven't done one before, does anyone know ifthis would have potential to improve performance or indeed any otherperformance enhancing techniques I might try.SELECT vehicle.vehicle_idFROM (( [vehicle]INNER JOIN [vehicle_subj_item_assn] onvehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)INNER JOIN [subj_item] on[vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)INNER JOIN [template_field] on[subj_item].subj_item_id=[template_field].subj_attr_idWHERE([template_field].template_field_id=@template_field_id) AND([template_field].template_field_type_id=3) AND([vehicle_subj_item_assn].subj_item_value_text=@value) AND(vehicle.end_dtm IS NOT NULL)ThanksGavin
View 3 Replies
View Related
Feb 8, 2008
I've never performed this type of installation. Any tips/ tricks you could provide would be greatly appreciated.
View 10 Replies
View Related
Aug 14, 2007
I would like my transformation to automatically create an output column for each input column. Any tips? I can't seem to determine which event to listen to or method to override.
View 3 Replies
View Related
Aug 21, 2006
SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
View 6 Replies
View Related
