How Can I Do Amalgamate 3 Select Queries And Then Get Unique Entries From The Result
Mar 7, 2006
Hi All
Strange request I know, but could somebody give me pointers on how I can put
3 queries into 1 'thing' and then get only the unique entries from this
'thing'.
To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queries
themselves aren't that complex and all return the same 2 fieldsets of stock
code and stock desc. Because these separate queries might bring back the
same stock code/description I need to amalgamate the data and then query
again to bring out only distinct stock values, eg:
Query 1 brings back:
stock code stock desc
IVP Invoice Paper
STP Statement Paper
KGC Keyboard Cover
etc... etc...
Query 2 brings back:
stock code stock desc
IVP Invoice Paper
BOB Back Pack
KGC Keyboard Cover
etc... etc...
I need to produce 1 resultset that shows:
stock code stock desc
IVP Invoice Paper
BOB Back Pack
3.5"D 3.5" Disks
KGC Keyboard Cover
STP Statement Paper
etc... etc...
(all unique entries)
I'm currently just bringing back the 3 query results in Excel, but I'd like
to be able to do the above.
In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?
I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.
I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".
Does this make sense?
EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....
SAGMIR 025 001 HEM/FIR HLF550...
SAGMIR 025 001 HEM/FIR KILN-D HLF505...
SAGMIR 025 002 HEM/FIR HLF660....
The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...
Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!
I have a table 'Group2Operation' that stores many to many relationsbetween the 'Group' table and the 'Operation' table (each group is haspermission to perform one or more of the available operations)PROBLEM=======I need to prevent duplicate entries being created. e.g. lets say thatin the 'Group2Operation' table a record links the 'editor' group tothe 'publish' operation. Should I prevent an administrator creating aduplicate of that record? (Otherwise deleting that permission willhave to be done twice or more for it to be effective)SOLUTION?=========So far I've done this with a trigger:CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2OperationFOR INSERT, UPDATEAS UPDATE Group2OperationSET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTagThe 'NoDuplicate' unique index column in the Group2Operation tablestores a concatenation of the unique group and operation identifiers.So when an attempt is made to create a record, the trigger is fired.If there is a duplicate, this will mean a duplicate entry in the'NoDuplicate' column. As a result, the INSERT or UPDATE will fail andthe duplication will be prevented.WHAT DO YOU THINK?==================What do you think? Am I going about this in the right way? Is atrigger a good way to do this or should I rely on application logic toprevent duplicates?Any help appreciated by this db novice.John Grist
[Phone] [Time] [Result] 5551212 0812 No Answer 5551212 1203 Answering Machine ... 5551213 0813 Busy 5551213 1634 No Answer
So as you can see, Phone contains phone number, Time contains time the number was called and [Result] contains the outcome. Now I need a query that gives me all the Phones on which the LAST outcome was No Answer. I don't need the Phone if it had a No Answer sometime during the day but not the last Result. I just need the last result. I need unique phone numbers obviously.
The Time field is DateTime. So am using it to sort right now. But I am pretty sure there's a better way.
I must also mention that I just have readonly rights and cannot create a new table if required.
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Hello all, i have a question i've beenk thinking for a little bit andcant seem to come up with a solution. What i want to do is add a uniquecounter to a resultset in query. For exampleSELECT ColumnA, ColumnB, ColumnC, ColumnDFROM AnyCombinationOfJoinsOrTablesWHERE SomeConditionIsTruewThe query above will produce a resultest of 4 columns.'A1', 'B1', 'C1', 'D1''A2', 'B2', 'C2', 'D2''A3', 'B3', 'C3', 'D3'......'An', 'Bn', 'Cn, 'Dn'Now what i want to do is add a unique identifier to every row. Theeasiest way would be to add a counter integer so the resultset wouldlook like1, 'A1', 'B1', 'C1', 'D1'2, 'A2', 'B2', 'C2', 'D2'3, 'A3', 'B3', 'C3', 'D3'......n, 'An', 'Bn', 'Cn, 'Dn'Is there a way to achieve this in T-SQL without using cursors.Thanks,Gent
heyas, i wanna know how can i select only entries with at least 2 rows? What i mean is that i want to make a select * from table1 where columnA = 'Something' order by columnB
but addicionally i want it to return only register that happen at least 2 times for columnB
SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category FROM (SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory FROM Categories GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories GROUP BY CountCategories.phoneNumber, CountCategories.category;
As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.It isn't showing me the unique result for the highest count.
I wrote 2 sql queries in two different ways. The only difference in both queries are One using '=' and other one using 'in'. All the other data is same.In my sense '=' and 'in' same Both queries should give same result. But one query giving different result and other query giving different result. Is this 2 queries different??
SELECT sum(SALES_ITEM.NET_PRICE) FROM PRODUCT_GROUP_LINK, PRODUCT, SALES_ITEM WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE) AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT) AND PRODUCT.STYLE_CODE='MC001' AND (SALES_ITEM.PRICE_LIST='1FAC' OR SALES_ITEM.PRICE_LIST='1FAW' ) AND CLASS_CODE='TYPE' group by STYLE_CODE
SELECT sum(sales_item.net_price) from PRODUCT, product_group_link, SALES_ITEM WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE) AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT) AND PRODUCT.STYLE_CODE='MC001' AND sales_item.PRICE_LIST IN ('1FAC','1FAW') AND CLASS_CODE='TYPE' group by style_code
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
I try to search my data and sort the result by importance.
I'm using a MS Access database and my data (table1) looks like this:
Code:
ID NAME TEXT 1 Apples Good red apples 2 Bananas Fine yellow bananas 3 Yellow apples Great yellow apples
I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:
Code:
SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*' UNION SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*' ORDER BY searchorder
The output is this:
Code:
ID NAME TEXT SEARCHORDER 3 Yellow apples Great yellow apples 1 2 Bananas Fine yellow bananas 2 3 Yellow apples Great yellow apples 2
So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.
Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?
select * from bigtable where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.
I currently have a stored procedure that selects sales entries from a table and inserts them into a temp table. To execute this procedure you must enter the store number and the dates required.
What I am looking to do is run this procedure daily for one store daily to load the previous days sales into the temp table.
I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables: If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice. The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application. Has anybody suffered this problem and what solution was adopted? Thanks for any information you can provide.
Country Category Internet Sales Amount Internet Order Count
[code]....
I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count from cust_table where datediff(month,full_date,(select max(full_date) from cust_table)) < 6 group by investor, full_date, month_end_summary, category, loan_count order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary from cust_table where datediff(month,full_date,(select max(full_date) from cust_table)) =0 order by investor, full_date
Can they be combined into one query to produce one result table??
Dear All, I have the following set of data, where FLD1, FLD2...FLD4 are the fields. I want to be able to select for each group FLD1,FLD2,FLD3 ORDER BY FLD4 ASC the top 2 entries
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into. Thanks
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL ------- SELECT job.job_number, (SELECT MAX(jsl.job_log_number) FROM job_status_log jsl WHERE job.job_number = jsl.job_number AND jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
We have a transaction record that, for the sake of brevity, I will usea simple paradigm to convey my need:A sales clerk sells several pairs of shoes over the period of a day,each & every day the clerk works. Each transaction is recorded ina database. All clerks use a single physical register but login tocapture unique ID. As each clerk logs in, it automatically logs outthe prior user and writes a zero-dollar record.Each record logs the RcdNbr, clerk's ID, date-timestamp and salesamount. (Other fields not pertinent to this discussion)Example:Rcd...CID...yyyy-mmdd.hhmm...Sale001...ABC...2005-0101.0850...10.00002...ABC...2005-0101.0930...00.00003...DEF...2005-0101.1000...15.51004...DEF...2005-0101.1200...00.00005...ABC...2005-0101.1300...12.83006...ABC...2005-0101.1530...00.00The above is unsorted. I would first sort by CID (ClerkID) to get:Rcd...CID...yyyy-mmdd.hhmm...Sale001...ABC...2005-0101.0850...10.00002...ABC...2005-0101.0930...00.00005...ABC...2005-0101.1300...12.83006...ABC...2005-0101.1530...00.00003...DEF...2005-0101.1000...15.51004...DEF...2005-0101.1200...00.00As a double-check of a clerk's actual worked hours we want to extractthe last record for each clerk for each day. In the above sample, I'dneed records 004 & 006.The transaction file covers 6 months worth of data.Is there a statement that I can construct that would extract the last(greatest?) time for each clerk for each day?
Hi, There is a table called tblPrices with fields Security_ID int Bid_Price decimal(12, 4) Ask_Price decimal(12, 4) Price_Quote_Date smalldatetime In this table there are thousands of records. For each Security_ID, there are several records with different Price_Quote_Dates What I would like to have is: A select query which shows every single Security_ID with the prices for the LATEST date that there is a price for that Security_ID Example: 1,100, 102.32, 1/12/2005 1, 43, 76.33, 6/12/2005 2,65.77, 45.76, 12/12/2007 ... ... ... Result 1, 43, 76.33, 6/12/2005 2,65.77, 45.76, 12/12/2007 ... ... ...
This is what I have started but it is not correct yet because the Security_IDs are repeated select Security_ID, Bid_Price, Ask_Price, max(Price_Quote_Date) from tblPrices group by Security_ID, Bid_Price, Ask_Price
Using DISTINCT with SELECT have effect only for one column. But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows Select DISTINCT a4 From Y ---> " 85 rows
I'm having problems executing TOP n queries on a database that was migrated from 6.5 to 7. I can get it to work on the Authors table in pubs, not in my other dbs. Here is an example:
CREATE TABLE dbo.tblsapParentCust ( Parent char (10) NOT NULL , Name varchar (40) NULL, IsSoldTo bit NOT NULL DEFAULT 0, CONSTRAINT PK_tblsapParentCust PRIMARY KEY CLUSTERED (Parent) ) GO
<load in some data>
SELECT TOP 10 * FROM tblsapParentCust
The select statement results in a syntax error:
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '10'.
I can switch over to pubs and change the query to reference the Authors table, and it runs fine.
If anyone can explain this behavior to me, I would appreciate it.
I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES).
I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.
In the database i have a table with this information:key_id =1key_desc =43+34+22+12I want sum the values in key_desc. Something like:SELECT key_desc FROM tableBut the result of the select was "111" and not "43+34+22+12".Is this posible?
Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.
Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:
SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1
