Limit And Order In Union Statement
Mar 18, 2008
hi all,
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?
-bm
View 6 Replies
ADVERTISEMENT
Jun 8, 2007
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
View 5 Replies
View Related
Jun 20, 2008
Hi,
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?
Any help is much appreciated.. thanks!!
mike123
How can I reproduce this results ?
View 7 Replies
View Related
Mar 19, 2008
hi,i have a union statement that 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] this statement selects all the records i want from both tables, and orders the entire new table by sort date.what i want to do is to add a limitation on the number of records. i have tried using both LIMIT and TOP commands, but i can't get it working. both of the below statements give me a syntax error saying "Incorrect syntax near the keyword 'ORDER'"first try:SELECT * FROM (<same_statement_as_above>) ORDER BY [SortDate] LIMIT 2second try:SELECT TOP 2 * FROM (<same_statement_as_above>) ORDER BY [SortDate]what am i doing wrong, and how do i achieve what i want?i am working with mssql server 2005 (express locally, and standard on my hosting service). thanks for your help!
View 2 Replies
View Related
Jul 22, 2013
I have a extremely large table and out of that I just need 2 records but based on some logic . For example
IF EMPLOYEE_ADDRESS <> NULL
set Employee_HAS_HOME to 1
ELSE
set Employee_HAS_HOME to 0
Here I can not use just first 1000 records as I am not sure If I will cover the second condition in first 1000 rows . Then I came up with solution like : -
select * from employee_table where employee_address <> NULL LIMIT 1
UNION
select * from employee_table where employee_address = NULL LIMIT 1
And this will give me 2 records which I can use for my testing . But unfortunately this is not working.
View 6 Replies
View Related
Sep 28, 2007
I wanted to use UNION and 'order by' in the same script but it keeps giving me error
eg;
select name,date
where name = 'sel'
union
select name,date
where name = 'ali'
order by name
I don't know where to put the 'order by'
Can anybody help me?
View 10 Replies
View Related
Feb 27, 2008
Hi
I have two tables which I'm combining.
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
View 3 Replies
View Related
Mar 12, 2008
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.
thanks
View 2 Replies
View Related
Jul 23, 2005
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
View 3 Replies
View Related
Aug 31, 2007
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
View 23 Replies
View Related
May 13, 2008
Hello,
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
View 3 Replies
View Related
Dec 7, 2007
Hi,
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?
Thanks!
Craig
View 5 Replies
View Related
Aug 16, 2006
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 ***
View 1 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Oct 18, 2007
Hi All,
I have this table in my DB.
ProductID | ProductSerial | Product Date
1 |FRT123 | 3/3/07
2 |EDR432 |5/5/07
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
Thank you
Sms
View 14 Replies
View Related
Jun 1, 2006
Hello,
I want to order by a field that is a code, like 1,2,3,11,12,13, and i get the following error
"Only text pointers are allowed in work tables, never text, ntext, or image columns sql server"
Why do i get this, what can i do to overcome this?
Thank you
View 7 Replies
View Related
Mar 1, 2006
Hi all,
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.
Thoughts?
Thanks!
View 14 Replies
View Related
Feb 10, 2015
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
[Code] ....
View 1 Replies
View Related
Oct 10, 2006
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'.
View 2 Replies
View Related
Nov 30, 2007
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)
Regards
Andrew
View 2 Replies
View Related
Jan 20, 2005
I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.
declare @OrderBy
@OrderBy = 'first_name'
Select first_name,last_name from table1
Union
Select first_name,last_name from table2
if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end
You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help
View 6 Replies
View Related
Apr 23, 2008
Hello again,
I am using UNION to return these 2 tables:
TableA
QID Q
1 Name?
2 Age?
3 Phone?
TableB
QID Q
1 DogName?
2 CatName?
When I use the following query;
SELECT * FROM TableA UNION SELECT * FROM TableB
I get the following return:
QID Q
1 whateverquestions
1
2
2
3
but I wish it to return in order of table invoke:
QID
1
2
3
1
2
Is that possible? (by not changing QID)
Cheers,
James
View 2 Replies
View Related
Oct 25, 2007
Hi!
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
View 4 Replies
View Related
Jun 15, 2006
I have the following as part of a union query:
CONVERT(CHAR(8), r.RRDate, 1) AS [Date]
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.
Thanks,
Dave
View 1 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Jun 3, 2008
Hello,Im wondering if someone can offer some advice, Im trying to get a union in the statement below to it will return all the data as rows in one table..... DECLARE @counter INT
SET @counter = 0
WHILE @counter < 24
BEGIN
SELECT "Hour"=(SELECT @counter),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
SET @counter = @counter + 1
ENDIf i write each statment out with the values as apose to @counter and union under each i get the result i am looking for... SELECT "Hour"=(SELECT 0),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
UNION
SELECT "Hour"=(SELECT 1),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
UNION
etc. etc. Does doing a while loop have any performace advantages to my other example? Thanks Bart
View 3 Replies
View Related
Oct 15, 2007
i have two machines. i was working in a "Execute SQL Task" object's SQL window on a rather long sql task on one machine and reached some kind of limit on the length of the sql statements. i can not add another line of code. i cut and pasted this same code into the exact same "Execute SQL Task" object's SQL window on the second machine and it does not have this limit. does anyone know what causes this? (in fact....i could paste it in twice - doubling the length)
View 2 Replies
View Related
Mar 31, 2006
I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed
This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);
This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;
I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;
Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.
NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID
DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932
DIEBOLD, INCOHUPS #88X08X18932
MY POINT: Is there a way to select a over all DISTINCT order ID.
Thank you for any help hope this make sense!
View 4 Replies
View Related
Aug 9, 2007
I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _
"UNION ALL" & _
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"
This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:
Invalid column name 'Towing'.
Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
View 5 Replies
View Related
Jul 3, 2007
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.
What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.
But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"
Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?
Thanks
View 2 Replies
View Related
Jun 5, 2004
What is the SQL Server equivalent of the LIMIT keyword available in MySQL?
SELECT * FROM MyTable WHERE ID = @ID LIMIT 1
this SQL does not work? I get an error saying that there is an error at keyword LIMIT. When I remove LIMIT 1, it works...
is there anything like this in SQL Server?
View 4 Replies
View Related
Jul 20, 2005
Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com
View 1 Replies
View Related
Jul 20, 2005
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View 5 Replies
View Related
