How To Get Resultset In The Order Of Items Given In IN Clause

Nov 1, 2007



My query is

select * from Items where ItemId in
(4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631)

I'm getting the resultant set in the random order.. like this


4354
14741
14742
14743
14744
14759
49798
58495
58496
62990
71631
105170
105244


How to retrive as same as i have given inside IN clause

i.e --> In this order 4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631

Any help would be very uself.

Thanks in Advance

Vidhya

View 6 Replies


ADVERTISEMENT

Order By Clause In View Doesn't Order.

May 18, 2006

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 16 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

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 4 Replies View Related

How Do I Add Items With Sql In A Specific Order

May 9, 2008

I need to create an ordered list of items using a select statement in a stored procedure. All itemsare obtained from a table but the first item in the list has a different value from the others.

View 10 Replies View Related

Rank Distint Items In Order Of Appearance

Feb 28, 2008



Hello,

I'm hoping someone can help me out with this. In order to add an sequence dimension in an ssas cube, I need to rank chronological item in a t-sql data set in order of their distinct appearance. I've tried to do this in mdx and there's just no easy way. I've also tried various ways in t-sql to achive, includeing ranking functions, custom udf, etc. To no avail.

The original dataset would look like this.

date, member_id, action
1/1/2008, 123, register
1/2/2008, 123, create_item_a
1/3/2008, 123, create_item_b
1/4/2008, 123, create_item_a
1/5/2008, 123, create_item_c
1/6/2008, 123, create_item_b

I need another column that flags the action in order of appearance, like so

date, member_id, action, appearance_rank
1/1/2008, 123, register, 1
1/2/2008, 123, create_item_a, 2
1/3/2008, 123, create_item_b, 3
1/4/2008, 123, create_item_a,2
1/5/2008, 123, create_item_c,4
1/6/2008, 123, create_item_b,3

So the "register" action would be the first thing a user did, "create_item_a" the second, create_item_b the third. However, the next occurrance of create_item_a would still retain the rank of 2 as it's not a new action for this user. The ranks would be unique to the actions, based on the first time they occurred.

This has been perplexing, but I feel like there is simple way to do this using some of the ranking functions, but just haven't been able to get it figure out.

Any help would be appreciated.

Regards...

View 5 Replies View Related

Display Multiple Items On One Line Per Order (was Query)

Jun 27, 2005

I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.

View 1 Replies View Related

Transact SQL :: Get List Of Items Present In Order Based On Confidentiality Code Of Product

Sep 29, 2015

I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.

I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.

whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.

If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.

Table 1:Order

Order_Id Order_No Customer_Id

2401 1234567 23
2402 1246001 24
2403 1246002 25

Table 2 : OrderedItems

OrderItem_Id Order_Id Item_Id Sequence

1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1

Table 3: ItemMaster

Item_Id Item_Name confidentCode

1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8

Out put for 1st grid 

**Note :** Logged-in user have confidentiality code 6

Order No Customer
1234567 23
1246001 24

3rd order is not displayed in the grid

After user selects the 1st order in the grid then the items present in that 1st order should be displayed as 

1001     Rice

the second item not displayed because that having confidentiality code greater than user.

After user selects the 2nd order in the grid then the items present in that order should displays

1005 Badham
1007 Oil

I need the query to display the order details in 1st grid.

View 3 Replies View Related

Reporting Services :: SSRS Report Does Not Display Resultset Through Dataset Returns Resultset

Oct 10, 2012

I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset  (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.

Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

View 4 Replies View Related

Order By Clause

Apr 30, 2001

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 1 Replies View Related

Order By Clause

Sep 5, 2007

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 7 Replies View Related

Reg. Order By Clause

Aug 19, 2006

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 1 Replies View Related

Order By Clause

Jul 20, 2005

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 1 Replies View Related

Order By Clause Problem

Aug 7, 2007

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 4 Replies View Related

A Special Order By Clause?

Aug 29, 2007

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 6 Replies View Related

T-SQL : Order By Clause Is Just Strange

Mar 19, 2008

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 2 Replies View Related

JOIN With ORDER BY Clause?

Dec 1, 2005

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 2 Replies View Related

Table Order By Clause

Mar 23, 2006

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 1 Replies View Related

Changing Order By Clause On The Fly

Jun 26, 2000

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 2 Replies View Related

Order By Clause Using A Variable

Mar 10, 1999

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 1 Replies View Related

Exception For An ORDER BY Clause

Feb 17, 2005

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 4 Replies View Related

Using Group By And Order By Clause

May 21, 2014

I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.

View 3 Replies View Related

Problems With ORDER BY Clause

Mar 29, 2004

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 1 Replies View Related

Top Clause , Union And Order By

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

SQL Query Help-- Order By Clause

Jul 20, 2005

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 7 Replies View Related

Problem While Using Order By Clause

May 12, 2008

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 2 Replies View Related

Order By Clause Using Parameters

Dec 12, 2007

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 9 Replies View Related

How To Use Order By Clause Row Wise

Sep 9, 2015

i want to know how to use order by clause in row wise

for example, suppose the rows is
10 20 30 40

so the output is should be
10
20
30
40

View 3 Replies View Related

Error In Use Of Order By In Over Clause

Sep 13, 2007



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 3 Replies View Related

Views Ignoring Order By Clause

Sep 7, 2007

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 16 Replies View Related

Need An Special ORDER BY Clause Query

Feb 25, 2008

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 5 Replies View Related

Remove Certain Words From ORDER BY Clause

May 22, 2008

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 8 Replies View Related

ORDER BY Clause With Multiple Tables

Aug 25, 2005

Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM   Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId                       LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View 6 Replies View Related

ORDER BY Clause Does Not Work In SQL 2005?

Feb 6, 2006

Hi,
A quick background on the problem;
My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place.
I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance.
Except....
None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio).
So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT...
If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code.
If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever.
To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED.
I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days!
Can anyone help?
 
Thanks always in advance,
Drew
 

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved