I am trying to combine 2 queries, each with their own 'order by' and I am having trouble.
This is just an example, not what I'm trying to do, my query is more elaberate but looks simular to this.
SELECT TOP 10 *, 'FieldA' AS SortedBy
FROM TableA
Order By FieldA Desc
UNION ALL
SELECT TOP 10 Precent *, 'FieldB' AS SortedBy
FROM TableA
Order By FieldB Asc
Anyway not to get the following error?
Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'UNION'.
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what from ( select date3, date2, date1, who, what from shows union select date3, date2, date1, who, what from shares union select date3, date2, date1, who, what from soiree ) a order by date3, date2, date1
I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.
Select employeeID as id, startDt as startDate from Table1 union all Select CustomerID as id, startDt as startDate from Table2 order by startDt
This works fine, unfortunately, my problem is I only want to display the first latest customer. In a Union All, how do i distinguish whether I'm ordering the table it is trying to union or the actual unioned table tiself?
For example, this does not work.
Select employeeID as id, startDt as startDate from Table1 union all Select top 1 CustomerID as id, startDt as startDate from Table2 order by startDt desc order by startDt
hello, I have 2 tables which contain a lot of articles, and they have same structure. what I want to do is that, select top 10 articles from each table and then union those records to another table, also I need to order these articles in desc order by their added date before i union them.
what I know is that for union sql phrase , there is only one order by is allowed, so how could I order those 2 tables before i union them.
I am having problems retrieving some data from a table,Tablename = "Magazine"ColumnsMagazineID MagazineName12 Times3 People4 National Geographic5 US6 Sports IllustratedI am trying to bind a dropdownlist in .net with the 4 newest magazinesand the empty magazine.SELECT TOP 1 MagazineID, MagazineName from MagazineUNIONSELECT TOP 4 MagazineID, MagazineName from MagazineOrder by MagazineName;The first part returns1,''The second part returns1,''2,'Times'3,'People'4,'National Geographic'Given the above data, how can I get the sql results to display5 'US'4 'National Geographic'3 'People'2 'Times'1 ''Essentially i need to do the followingSelect Top 1 MagazineID, MagazineName from Magazine Order by MagazineIDand add to itSelect Top 4 MagazineID, MagazineName from Magazine Order by MagazineIDdesc
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
I am trying to use dynamic ORDER BY with UNION in a stored procedure but I keep getting this error message:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The ORDER BY works fine without the UNION, and the UNION works fine without the dynamic ORDER BY (just putting an ORDER BY works fine)
Here is the whole query in case this helps, thank you
SELECT leave_id, leave_type, annualLeave.from_date, convert(nvarchar(10), annualLeave.request_date, 103) as dated, convert(nvarchar(10), annualLeave.from_date, 103) as from_date, convert(nvarchar(10), annualLeave.to_date, 103) as to_date, annualLeave.canceled, annualLeave.working_days, staff.staff_name, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' ELSE '' END AS pendingStyle, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' ELSE '' END AS historyStyle FROM annualLeave INNER JOIN staff ON annualLeave.staff = staff.staff_id WHERE staff.department like @Department and (@staffID is Null or annualLeave.staff = @StaffID) and (@Canceled is Null or annualLeave.canceled = @Canceled) and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null) and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null) and (@StartingDate is Null or annualLeave.from_date > @Startingdate) and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave) and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)
UNION
SELECT leave_id, leave_type, annualLeave.from_date, convert(nvarchar(10), annualLeave.request_date, 103) as dated, convert(nvarchar(10), annualLeave.from_date, 103) as from_date, convert(nvarchar(10), annualLeave.to_date, 103) as to_date, annualLeave.canceled, annualLeave.working_days, staff.staff_name, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' ELSE '' END AS pendingStyle, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' ELSE '' END AS historyStyle FROM annualLeave INNER JOIN staff ON annualLeave.staff = staff.staff_id WHERE staff.department like @Department and (@staffID is Null or annualLeave.staff = @StaffID) and (@Canceled is Null or annualLeave.canceled = @Canceled) and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null) and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null) and annualLeave.leave_type like 'Leave in lieu' ORDER BY CASE @OrderDir WHEN 'ASC' THENannualLeave.from_date END ASC, CASE @OrderDir WHEN 'DESC' THEN annualLeave.from_date END DESC
I have the following query and for some reason when I try to run it, it tells me there is an incorrect syntax near the 'union' statement. Can you tell me what is wrong with this?
SELECT MDN, DATEPART(d,CallDate) as CallDate, DATEPART(hh,CallDate) as Hour, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN UNION SELECT MDN, DATEPART(d,CallDate) as CallDate, DATEPART(hh,CallDate) as Hour, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.
The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):
QUERY 1
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s ON ES.SchId=S.SchId JOIN vehicles v ON v.vehicleid=es.vehicleid JOIN Events PO ON PO.schid=es.schid AND PO.EvStrId=ES.EvStrId AND po.activity=4 JOIN Events PI ON PI.schid=es.schid AND PI.EvStrId=ES.EvStrId AND pi.activity=3
WHERE es.providerid in (0,1,4) and s.ldate>=? and s.ldate<=? and v.mdtid=20411
order by s.ldate desc
RESULTS 1
DATE MDT IDPU Odometer DO Odometer Total Miles 12/6/2007 2041112810.6 12874.5 63.9
QUERY 2 (with Union)
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s ON ES.SchId=S.SchId JOIN vehicles v ON v.vehicleid=es.vehicleid JOIN Events PO ON PO.schid=es.schid AND PO.EvStrId=ES.EvStrId AND po.activity=4 JOIN Events PI ON PI.schid=es.schid AND PI.EvStrId=ES.EvStrId AND pi.activity=3
WHERE es.providerid in (0,1,4) and s.ldate>=[From Date,Date] and s.ldate<=[To Date,Date] and v.mdtid=20411
Union
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s ON ES.SchId=S.SchId JOIN vehicles v ON v.vehicleid=es.vehicleid JOIN Events PO ON PO.schid=es.schid AND PO.EvStrId=ES.EvStrId AND po.activity=4 JOIN Events PI ON PI.schid=es.schid AND PI.EvStrId=ES.EvStrId AND pi.activity=3
WHERE es.providerid in (0,1,4) and s.ldate>=? and s.ldate<=? and v.mdtid=2642
order by s.ldate desc
RESULTS 2
DATE MDT ID PU OdometerDO Odometer Total Miles 4/10/2007 20411 1207.21252.5 45.3 1/2/2007 2642 193652.6193817 164.4
As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?
SELECT *FROM tblCountryWHERE Country_Id = 26UNION ALLSELECT *FROM tblCountryWHERE Country_Id <26--order by country_Namei need to select country_id =26 and then the rest i want them ordered byname.if i put order by country_name, the country_id 26 isn't displayed as thefirst one.is there anyway to apply the order by only to the second select not thewhole?*** Sent via Developersdex http://www.developersdex.com ***
All,I've seen several posts regarding using UNION or UNION ALL to mashtogether two or more resultsets into a single result set, but can'tseem to find enough info here to help me answer my particularquestion.I have a stored procedure that gets the column names in a particularformat (i.e. "chassis_id"|"chassis_description"|"modify_date") as wellas actual data for a given table (in a quote-separated, pipe-delimitedmanner i.e. "1"|"description for the chassis"|"2004-09-08").I'd like to get both of these resultsets and mash them together. Thisworks, but when I need to order the second resultset (i.e. select *from chassis order by chassis_id), SQL Server returns an errorcomplaining about the chassis_id column name (invalid column name'chassis_id') in the Order By clause.From what I can tell, I'm using the UNION and Order By in correctly,but I'm not sure exactly what's wrong with it. If I take out the OrderBy, everything works great. Although I would like to be able to ordermy second resultset (in the same sproc) if possible.The actual queries I'm running are actually quite long, but here's onethat's a bit shorter to help illustrate:SELECT '"app_group_id"|"app_group_name"|"create_date"|"create_by"|"modify_date"|"modify_by"'UNION ALLSELECT + ISNULL(CONVERT(varchar,app_group_id), '') + '|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 1))), 1,0)+ '"' + ISNULL(CONVERT(varchar(1000), +REPLACE(CONVERT(nvarchar(4000),app_group_name), '"', '""')), '') +'"|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 2))), 1,0)FROM app_grouporder by app_group_idThank for any help on this./bc
I need to run two select statment on the same tabel and i Use Union to join the result comes from the first table with the one comes from the second one. but one i use order by union dose not work.
my question is : How Can i run the two select statment on the same table and join the results and order each select?
my queries: Select ProductID,ProductSerial,ProductDate Where productID = @ProductID Order by ProductID
Union Select ProductID,ProductSerial,ProductDate Where productID = @ProductID Order by ProductID
I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).
I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).
This select fails, because it doesn't like the ORDER BY in the subqueryselect CASE WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN 'MON' WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN 'FRI' END AS Dow, 'N/A' AS Freight UNION ALL (select top 4 CASE WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN 'MON' WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN 'FRI' END as DOW, CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )
I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?
I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM ((select GETDATE() as [OrderDate], CASE WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN 'MON' WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN 'FRI' END AS Dow, 'N/A' AS Freight ) UNION ALL (select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM (select top 4 [OrderDate] as [OrderDate], CASE WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN 'MON' WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN 'FRI' END as DOW, CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.
My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.
This query works perfectly and orders by just as I need
Code: Select '1st' As [Type], #Uno.ID #Uno.Address, #Uno.shippingInfo FROM #Uno
[Code] ....
However, when I use it in a Union All so I can pull data from 2 diff tables, the order by statement no longer works. How can I order by data in 2 tables?
Code: Select '1st' As [Type], #Uno.ID #Uno.Address, #Uno.shippingInfo FROM #Uno
I'm trying to write a UNION statement with 2 selects and can't figure out for the life of me how to do it.
select top 10 * from tblusers ORDER BY dateJoined UNION select top 10 * from tblusers ORDER BY lastLogin
The union works fine without the order by's, but I really need the order by's in there and I don't understand why its so difficult to do. Surely there must be some other strategy?
Hi, Can anyone help as to how to get this to work? dbo.parseInt is a Function which sorts Alpha Numerically. If I removed the top part of the UNION, the Procedure works fine.
ALTER PROCEDURE dbo.ByJobNoAlphaNumeric as SELECT 0 AS JobID, '<All Jobs>' AS JobNo UNION SELECT JobID, JobNo FROM tbl ORDER BY dbo.parseInt(JobNo)
i have the following union statement, which works like a charm:SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel ORDER BY [SortDate]
now i want to ad a limit to this statement, but i can't get it working. i have tried this:SELECT * FROM (SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel) ORDER BY [SortDate] LIMIT 2 but i keep getting an error message saying "Incorrect syntax near the keyword "ORDER". what am i doing wrong?
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan
Hi Everyone This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE, CC.[COMPANY], CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE] FROM CUSTOMERCONTACTNOTES AS CCN, (SELECT * FROM CUSTOMER) AS CC, (SELECT MAX([INVOICE DATE]) AS LASTORDERDATE, [ACCOUNT REFERENCE] FROM INVOICEDATA GROUP BY [ACCOUNT REFERENCE]) AS INVDATE WHERE CCN.[COMPANY] = CC.[COMPANY] AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA) AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE] GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE] ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment. Mitesh
I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell
I need help with a union query. My table structure is as follows: OffierID (key field) CaseFileID CurrentOffer PrevOffer
There table can have multiple entries per CaseFileID. I need a query that will tell the highest value in Current Offer or PrevOffer for each CaseFileID. I have a union query that combines CurrentOffer and PrevOffer and then selects the top value for a specific CaseFileID; however, I want to have a complete list of CaseFileIDs with one value for PrevOffer. My current query is as follows:
SELECT TOP 1 Offer FROM ( select CurrentOffer as PrevOffer FROM tblOffers WHERE tblOffers.CaseFileID = @CaseFileID AND CurrRank <> 1 UNION select PrevOffer as PrevOffer FROM tblOffers WHERE tblOffers.CaseFileID = @CaseFileID ) tmp ORDER BY 1 desc
How can I get this to work for all CaseFiles? Thanks for your help.
Hi gusy, this is the first time I am trying to use "Union" query. I am trying to create a view(linking and taking data from 3 tables) so I can create a crosstab report out of it.
Basically one table contains about 12 fields and I am trying to grab data from all of them is they are not null.So this is my query,but when it executes it only dispalys result from the first query,what am I doing wrong.
SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID, dbo.RelocateeRemovalistAmounts.CarInsurance FROM dbo.RelocateeRemovalist INNER JOIN dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID WHERE (dbo.RelocateeRemovalistAmounts.CarInsurance IS NOT NULL) UNION SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID, dbo.RelocateeRemovalistAmounts.CarTransport FROM dbo.RelocateeRemovalist INNER JOIN dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID WHERE (dbo.RelocateeRemovalistAmounts.CarTransport IS NOT NULL)
I've been trying to do a union with a subquery - I've made a differentexample which follows the same principles as follows:First bit brings back accounts which are in the top 10 to 15 by accountname.Second bit brings back accounts which are in the bottom 10 to 15 byaccount name.I want to union the two result sets together. These selects work asthey are, but don't when i take the comment away from the unionoperator.select top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName asc)order by c1.accountName asc--union allselect top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName desc)order by c1.accountName descSo my problem is really about how to have an order by in a sub querywhich is then used in a main query which is then unioned with anotherquery - SQL Server doesn't seem to like that combination of things. Anyclues anyone?Cheers,NAJH
hi,Can you union 2 queries with an IF statement between them?e.g.select a, bfrom mtTablewhere a = cunionif ab = xbeginselect a, bfrom mtTablewhere a = cendCheers,Jack
