Order By Because Of Distinct

Feb 23, 2004

I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query:

select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2753
100 2753
100 2071
100 2753

Now I change the query to:

select distinct ofd_fk_off_id, ofd_fk_class_id from
(select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2071
100 2753

In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.

View 14 Replies


ADVERTISEMENT

Distinct With Order By

Mar 28, 2007

It gives me this error:
ORDER BY clause (TimeStamp) conflicts with Distinct

The sql statement is:

sql Code:






Original
- sql Code




SELECT DISTINCT division from table order by Stamp DESC






SELECT DISTINCT division FROM TABLE ORDER BY Stamp DESC



I've tried:

sql Code:






Original
- sql Code




SELECT DISTINCT division, Stamp FROM table ORDER BY Stamp DESC






SELECT DISTINCT division, Stamp FROM TABLE ORDER BY Stamp DESC


But this produces duplicate rows, which in this specific case is unacceptable.

Is there any way I can acheive what I want?
Stamp is a TimeStamp if you couldn't figure it out. I just want the last one entered, but all the last ones entered per division. . .

View 9 Replies View Related

How Do I Use Order By When I Use Select Distinct.

May 6, 2008

Hi
    I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc
 DECLARE @Counter TABLE(
PlanId int,
FundId int,
ClientFundName varchar(110),
DisplayOrder int IDENTITY(1,1),
IsDefault bit,
IsPortfolioFundOnly bit
)
INSERT INTO @Counter
(
PlanId,
FundId,
ClientFundName,
IsDefault,
IsPortfolioFundOnly
)
SELECT
5923,
f.FundId,
d.FundName,
CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END,
CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END
FROM
PlanDetail d
INNER JOIN Statements..Fund f
ON d.CUSIP = f.CUSIP
OR
d.Ticker = f.Ticker
OR
d.Ticker = f.ClientFundId
OR
d.CUSIP = f.ClientFundId
-- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether
--fund can only be chosen as part of a portfolio.
LEFT JOIN PlanDetail p
ON d.FundName = p.FundName
AND
d.PortfolioName = p.PortfolioName
WHERE
d.PlanNumber IS NOT NULL
AND
p.PortFundPercent IS NULL
GROUP BY
f.FundId,
d.FundName,
d.FundDefault,
--d.PlanNumber,
--d.Cusip,
-- d.Ticker,
--d.RowNumber,
p.FundType

ORDER BY
Min(d.PlanNumber),
Min(d.RowNumber)


 any help will be appreciated.
Thanks
Karen

View 1 Replies View Related

SELECT DISTINCT W/ORDER BY

Oct 6, 2005

I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT DISTINCT
pdm.Account,
pdm.Customer
FROM
dbo.Demands pdm LEFT OUTER JOIN
dbo.Tickets rct ON pdm.Account = rct.Account
WHERE
pdm.Code IN (66, 51)
ORDER BY
pdm.TransactionDate DESC

Is there any way to make the ORDER BY work in this case?

View 1 Replies View Related

Distinct & Dynamic ORDER BY

Apr 29, 2008

Hello,

I have a stored procedure with dynamic ORDER BY. I would like to use the DISTINCT too. Is it somehow possible?
Thank you

Here is the stored procedure:

SELECT identifier_company + cast(identifier_number as nvarchar(3)) as identifier,
CASE WHEN canceled = 'True' THEN 'canceledPO' ELSE '' END AS style,
staff.staff_name,
purchase.purchase_id,
purchase.traveller_name,
nominal_department.department_name,
purchase.canceled,
purchase.travel_date,
convert(nvarchar(20), purchase.date_raised, 103) as dated,
supplier
FROM purchase INNER JOIN purchase_project ON purchase.purchase_id = purchase_project.purchase_id
INNER JOIN staff ON purchase.raised = staff.staff_id
INNER JOIN nominal_department ON purchase.department = nominal_department.nominal_dep_id
WHERE (raised in (SELECT staff_id FROM staff WHERE department like @FromDepartment) or purchase.raised = @raisedBy)
and purchase_project.project_number like '%' + @Query + '%'
ORDER BY
CASE @SortDir
WHEN 'ASC' THEN
CASE @OrderBy
WHEN 'staff_name' THEN cast(staff_name as nvarchar(100))
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'department_name' THEN cast(department_name as nvarchar(100))
WHEN 'supplier' THEN cast(supplier as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(10))
END
END
ASC,
CASE @SortDir
WHEN 'DESC' THEN
CASE @OrderBy
WHEN 'staff_name' THEN cast(staff_name as nvarchar(100))
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'department_name' THEN cast(department_name as nvarchar(100))
WHEN 'supplier' THEN cast(supplier as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(10))
END
END
DESC

View 3 Replies View Related

DISTINCT Requires ORDER BY

Jun 28, 2007

OK I am trying the following select statement but I am getting a 'DISTINCT requires ORDER BY to be used' error. I have an ORDER BY in it so I am not sure what I have missed?

SELECT DISTINCT tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum AS [Full Cost]
FROM tbl_final_CP INNER JOIN
ContractorAreaRelationship ON tbl_final_CP.Area = ContractorAreaRelationship.AreaNo INNER JOIN
tbl_CPCost ON tbl_final_CP.CP = tbl_CPCost.CP
WHERE (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND
(DATENAME(mm, tbl_final_CP.dCount) = 'Conwy') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND
(DATENAME(mm, tbl_final_CP.dCount) = 'March') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND
(ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) AND (ContractorAreaRelationship.AdminID = 6112) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND
(ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) AND (ContractorAreaRelationship.AdminID = 6112)
ORDER BY tbl_final_CP.CP, tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum

View 2 Replies View Related

SQL Distinct Value And Order By Some Field......

Jan 4, 2006

Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan

View 2 Replies View Related

SELECT DISTINCT Type ORDER BY

Nov 26, 2007

I am trying to display items from a table that have a type. For each condition there are between 7 and 10 types. I am looping through each type and displaying all items in that type. I am using DISTINCT to pull the types and count them, so I know how many there are and how times to loop. My problem is that DISTINCT is ordering the types alphabetically! I want them in the same order they went into the table. I tried adding ORDER BY primaryID, but got an error that said I also had to select primaryID as well as type, so now I am selecting every item that fits the condition and not just the DISTINCT types! Is there any way to make it order by column_position? I am using SQL 2K.

View 9 Replies View Related

How Can I Use SELECT DISTINCT And Maintain The Original Order

Apr 26, 2007

Say I have a result set with two fields numbers and letters.

1 A3 A1 B2 B


 The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet

it will reorder the new result set by the Number field and return

123

 However, I'd like maintain the Letter order and return

132

View 1 Replies View Related

SELECT DISTINCT MyId Order By MyDate

Nov 3, 2003

I have the following records,
MyDate MyId
2003/10/25 2
2003/10/26 3
2003/10/26 1
2003/10/27 3
2003/10/28 2
2003/10/29 4

I want to get the most earlier date distinct records.
I try to use "SELECT DISTINCT MyID from Table;"
I expect to get Myid: 2,3,1,4
But the computer returns Myid:1,2,3,4

Is there a way to get the records using
"SELECT DISTINCT MyID from Table ORDER BY MyDate;"

Appreciate help......

View 6 Replies View Related

SELECT DISTINCT And ORDER BY With Aliased Column

Feb 14, 2008



This query demonstrates a problem I have run across:


USE AdventureWorks

GO

-- This query works fine.

SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query also works fine.

SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query returns error 145

SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO


The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".
It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it.
Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.

View 8 Replies View Related

Extract Distinct Information And Order The Results

Sep 24, 2007

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.


I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description
5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.


SELECT * FROM SAMPLE
WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query
5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards
Deceptive

View 9 Replies View Related

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?

Mar 22, 2008

Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View 10 Replies View Related

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache



When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."



Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View 2 Replies View Related

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

Mar 12, 2007

I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks

View 2 Replies View Related

DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?

May 8, 2006

Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of  the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table,  where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.

View 4 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

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

SQL To Order Results In Predefined Order

Mar 27, 2008

I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?

View 4 Replies View Related

Force The ORDER BY To Be In Ascending Order??

Jul 5, 2007

I noticed the StockDate is not sorted in proper order, like ascending order...


Code:

select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc



I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...

The sample data is MM/DD/YYYY...

So, how do I get it ordered propery by Year, Month then Day??

View 2 Replies View Related

Specify Order For Select Results, Order By: Help!

Nov 17, 2006

Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View 1 Replies View Related

In-Order/Level Order Etc. Traversal Using CTE

Apr 16, 2008

Hi,

I have some hierarchical data in a table. Say for example:

Parent Child
------------------------
NULL 1

1 2

1 3

2 4

2 5

3 6

3 7

5 8

5 9

7 10

7 11

11 12

11 13


Now I want to be able to use CTE's to be able to traverse this tree in
1) level by level order 1,2,3,4,5,6,7,8,9,10....
2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13...

What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well.


with Tree (id)

as

(

select id from WithTest

where parent is null

union all

select a.id

from Tree b join WithTest a

on b.id = a.parent



)

select * from Tree


Any ideas? Thanks.

View 3 Replies View Related

Order ID For Latest Order For Every Customer

Jan 8, 2008

Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.

Any ideas?

Thanks,
Assaf

View 1 Replies View Related







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