MS SQL Server :: Top Clause , Union And Order By

Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons

Any ideas?

View Replies !

Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT     TOP (100) PERCENT dbo.UWYearDetail.*,  dbo.UWYearGroup.*
FROM         dbo.UWYearDetail INNER JOIN
                      dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
                      dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View Replies !

Union Clause
sql = "Select c1 from t1
sql = sql & " Union Select c1 from t2 Order By c1"

t1=c1 = 1,2,3
t2=c1 = 200,5,300

datatype=nvarchar

result is not ordered as 1,2,3,4,200,300 why

View Replies !

SQL Union With ORDER BY
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 Replies !

Union & Order By
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 Replies !

UNION ALL And ORDER BYs
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 Replies !

How To Use UNION And 'order By' Together?
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 Replies !

Using Group By Clause In Union Query
Hi all,

I have two table having datas like

Table1
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    0    3    2
x 0    1    0    2
x 0    0    2    1
y             1              5              2                0

Table2
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    0    1    4
y             1              0              3                1
y             1              2              0                0
y             0              0              5                1

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t

Result:
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    1    5    5
y 1    5    2    0
x 0    0    1    4
y 2    2    8    2

But i need the result like i.e grouped by column 'A'
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    1    6    9
y 3    7     10     2

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t group by A

The above query gives the following error
[Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help me out.

-Anand

View Replies !

UNION: Select Order
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 Replies !

Union All And Order By Problem
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 Replies !

Order By In Subquery + UNION
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 Replies !

Error On Order By With Union All
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 Replies !

Can I User Union With Order By?
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 Replies !

Union And Order Statement
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 Replies !

Dynamic ORDER BY And UNION
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 Replies !

ORDER BY In Subquery Of A UNION Fails ???
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 Replies !

UNION With Dynamic Order By Failing
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 Replies !

[ Resolved ] Union, Each Query W/ Own Order By
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 Replies !

Limit And Order In Union Statement
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 Replies !

Writing Union Statement With 2 Order By's
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 Replies !

Multiple Order By Statements In Union Select
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 Replies !

Order Converted Dates In Union Query
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 Replies !

Query Multiple Tables - Union/Order By
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 Replies !

Returning In Table Called Order Using UNION
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 Replies !

Order By Clause
If I use the order by clause to sort on a date, where the date andtime stamp are the exact same for multiple records, how does SQLoutput the data?At random... or does it look at the primary key?

View Replies !

Order By Clause
Hello,
can any one tell me about the difference between the following queries.

1. SELECT * FROM Symp_User ORDER BY
2. SELECT * FROM Symp_User ORDER BY ASC

I don't think there is any difference in the above queries. kinldy make me clear on this.

thnkx,
rahul jha

View Replies !

Order By Clause
Hi there,
i'm trying to order the follow query:

SELECT count(nome) as CNT FROM utenti WHERE nome like '%" & rs("nome") & "%' ORDER BY CNT ASC

why i've this error:

Microsoft OLE DB Provider for ODBC Drivers errore "80040e10"
[Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

Thank you in advance...

View Replies !

Order By Clause
I am looking to get a cyclic order in the order by clause. how do I do this?

for example I have 5 customers with ids like
xyz 1
xyz 2
xyz 3
xyz 4
xyz 5

when I am selecting xyz 3 I want the list to show
xyz 4
xyz 5
xyz 1
xyz 2
xyz 3

How do I do this by using the order by clause?

View Replies !

Order By In() Clause ??
Hi!

My problem is this one:

I'm doing a simple query like this (simplified):

SELECT Me_id, Det_id, Det_val
FROM oFormsMsgDet
WHERE (Det_id IN (2411, 2409, 2410, 2408))

And I want the recordset order by my IN list. My In list is given dynamicly so I can't play with it and my query is returned in Det_id order. How can I do it?

Thanks a lot

Genviou

View Replies !

Reg. Order By Clause
Can you explain the below scenario

The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

what is the reason behind this.

View Replies !

Order By Clause Problem
Hello,Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8....98....0,0,0My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?Thanks, Curt.

View Replies !

T-SQL : Order By Clause Is Just Strange
Hi! I think the order by clause is driving me crazy.The following T-SQL query works: SELECT
count(*) AS c
From F_POST
Where id=@id
Order by c Ok, so far so good, but in the following case it is NOT possible to order the result set according to "count(*)": Select
T_Date AS TDATE,
count(*) AS c
From F_Post
Where id=@id
Order By case when @OrderBy = 1 then c
elseT_DATE DESC This is just strange since it is essentially the same query!? Furthermore, it seems to be inpossible to have a order-by-clause that looks like: Order By case when @OrderBy = 1 then T_Column1 ASC
elseT_Column2 DESC

Does anyone know how I can implement querys that do (almost) exactly this what the last 2 querys "should" to do?

View Replies !

A Special Order By Clause?
The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?
SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1 ORDER BY Country_Long

View Replies !

Table Order By Clause
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results.
Any way, we can fix it.
i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.

View Replies !

JOIN With ORDER BY Clause?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?

View Replies !

SQL Query Help-- Order By Clause
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks

View Replies !

Problems With ORDER BY Clause
Need to pass column to ORDER BY as parameter in sp (possible 8 columns out of total 30). Is there a way to do it avoiding dynamic SQL use(will be used frequently)?

View Replies !

Order By Clause Using A Variable
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work.

Here is the statement:

Create Procedure sp_InfoDump
(
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MinDate datetime, @MaxDate datetime

IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MinDate = Min(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MinDate = @StartDate
END

IF @EndDate = 'ALL DATES'
BEGIN
SELECT @MaxDate = Max(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MaxDate = @StartDate
END

BEGIN
SELECT tblReports.ReportName, tblReports.ReportCode,
tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime,
tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime,
tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime)
FROM tblReports
INNER JOIN
tblReportsAccess ON
tblReports.Report_ID = tblReportsAccess.Report_ID
INNER JOIN
tblAudit ON
tblReportsAccess.Audit_ID = tblAudit.Audit_ID
WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate
ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy)
END

RETURN

View Replies !

Changing Order By Clause On The Fly
I'm using a Case statement to change an Order By clause on the fly, eg

ORDER BY case when @SortBy = 1 then s.ITEM_NAME
when @SortBy = 2 then s.ITEM_ID
when @SortBy = 3 then s.ITEM_SIZE
end

The numeric columns work fine but when @SortBy = 1, I get the following message when I try to run the sp:

Server: Msg 8114, Level 16, State 5, Procedure usp_CML_SAO_RptPresLvl, Line 95
Error converting data type varchar to numeric.

ITEM_NAME is a varchar(40) containing alphanumeric characters; ITEM_ID is a numeric(8,0) & ITEM_SIZE is a varchar(5) containing numeric characters.

Is there some rule preventing me to dynamically change the Order By if using a alphanumeric characters?
Thanks for any help you can offer
Jo

View Replies !

Exception For An ORDER BY Clause
I have a query that returns several ordered rows where one of the fields in the ORDER BY clause is a date field (DueDate) that we use to see the most pressing deadline first. The problem is that the default value in that field (which other code translates to mean no due date) is 1/1/1900. That means that items with no due date show up before today's import deadline. I can see one potential solution where I join my results on the original table where DueDate>1/1/1900 and then back to my results so I can use an ISNULL() on the field to set a value in the future (like 1/1/9999), but that seems like a really nasty wrong round-about way to do it. I think there has to be something better.

View Replies !

Problem While Using Order By Clause
Hi,
Im using a select query in which im using order by clause on a column which is varchar.
Im getting wrong result on using the query,
the result output is below

1036
1373
1610
2324
255
2819
324
459
477
581
698
831

can anyone help

View Replies !

Order By Clause Using Parameters
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.

In the query I want to set the Order By clause based on the user selection. eg.:

select * from dbo.customers where name = @CustomerName order by @SortBy

However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work

="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy

select * from dbo.customers where name = @CustomerName order by + @SortBy

I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.

Any input would be appreciated.

Thanks!
Arpan

View Replies !

Error In Use Of Order By In Over Clause

Hi,
I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,
Emil

View Replies !

Remove Certain Words From ORDER BY Clause
Hi,I'm trying to remove certain words from my Order clause. For example on iTunes they have removed the word 'The' from the start of artist names so that all the bands that start with 'The' don't appear grouped together. I'm trying to do a similar thing with University names so that all universities which begin with 'University of' or 'The' don't appear together.Is this possible?Thanks for your help!,Curt.

View Replies !

Need An Special ORDER BY Clause Query
Table:ColumnsUsersList:UserID, UserName, Country
I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order
So the query first return all the users from Pakistan and the the users from rest of the world in ascending order.
Forexample,
1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan
then the query returns.
3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA

View Replies !

Views Ignoring Order By Clause
I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want.
I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code.
Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables.
Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC
If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order.
When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order.
Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly?
Regards, Robert Barnes

View Replies !