• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

SQL Sorting

I have the following scripts that renumber a column based on the sort order of another column. it also allows for a starting number so I can get a TOP(n) number of records on top of the list and then sort the reamining items in alphabetical order.

I am trying to figure out why sometimes it does not order the result correctly. for Example, it puts "Lancia" after "Landrover". I am suspecting that the "where t2.make < t.make) is not returning the correct result and it causing this to happen.

If there is better/simpler way to do this, I would appreciate any input.

Make is a varchar(50)
OrderNumber is an int

DECLARE @StartingNumber integer
set @startingnumber = 6

update t
set t.ordernumber = (select count(1) from MakeTable t2 where t2.Make < t.Make) + @startingnumber
From makesandmodels t
 WHERE t.OrderNumber > 7

0
sleiman
Asked:
sleiman
  • 15
  • 10
  • 7
  • +2
1 Solution
 
tigin44Commented:
you can use the ROW_NUMBER function to clarify this process i.e.


SELECT make
FROM (
	SELECT make, ROW_NUMBER() OVER (PARTITION BY make ORDER BY someColumn) AS orderNumber 
	FROM makesandmodels )A
WHERE orderNumber = 7

Open in new window

0
 
SharathData EngineerCommented:
Can you provide some sample data with expected result?
0
 
sleimanAuthor Commented:
Lexus  1
Honda 2
Toyota 3
Acuara 4
Audi 5
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
Are you fine with OrderNumber? Those are not in alphabetical order. Also what is the expected result?
0
 
sleimanAuthor Commented:
I need to have the sort start after Toyota because I want 1,2,3 to be numbered as they show in the sample. the update script need to start renumbering after the 3rd row and use the make as a sort (alpha).
0
 
sleimanAuthor Commented:
I may have posted this under SQL 2005. It is actually a SQL 2000.

tigin44: the ROW_NUMBER() is not working. (is not a recognized function name)

Also, I need the update script not a select script.
0
 
SharathData EngineerCommented:
Are you looking for something like this?
create table #table(Make varchar(10),OrderNumber int)
insert #table values('Lexus',  1)
insert #table values('Honda', 2)
insert #table values('Toyota', 3)
insert #table values('Acuara', 4)
insert #table values('Audi', 5)

-- Before Update
select * from #table order by OrderNumber
/*
Make	OrderNumber
Lexus	1
Honda	2
Toyota	3
Acuara	4
Audi	5
*/
update t1
  set t1.OrderNumber = (select COUNT(1) from #table t2 where t2.Make <= t1.Make)
  from #table t1
-- After Update
select * from #table order by OrderNumber
/*
Make	OrderNumber
Acuara	1
Audi	2
Honda	3
Lexus	4
Toyota	5
*/
drop table #table

Open in new window

0
 
RyanSandbergCommented:
The reason it did not return alphabetically the first time is that the count function returns a bag (unordered collection)
Bags do not guarantee order of any kind unless explicitly ordered
0
 
sleimanAuthor Commented:
Hi Everyone. Please read my request to have the first (n) number of rows sorted as desired. Meaning in the example below, I want Lexus, Honda, Toyota to be on top of the list. Anything else that comes after the first three of (n) number, should be sorted alphabetically and with an order number to follow 3. this is an example. Sometimes, I need the top 5 or top 10 to be controlled manually and then everything else after that sorted alphabetically. (see the script in the original post)

Lexus  1
Honda 2
Toyota 3
Acura 4
Audi 5
0
 
SharathData EngineerCommented:
Do you want this ordering just for display purpose or you want to update the table with new order?

>> Please read my request to have the first (n) number of rows sorted as desired.
How do you determine the first n number of rows in a table?
0
 
sleimanAuthor Commented:
I need it to update the table with the new order.
0
 
SharathData EngineerCommented:
You did not answer the 2nd question. How do you determine the first n number of records.

if you run a query like select top 6 * from table1 and get 6 records, it does not mean that those are first 6 records. There is no meaning to TOP clause until you mention some ORDER BY column.
0
 
sleimanAuthor Commented:
Please refer to my original question/script. I use a startingnumber as a var. So, it is set to 3, I am telling the script to ignore 1,2,3 and start ordering the new list with number 4. Does this make sense?
0
 
SharathData EngineerCommented:
Which 3 records are going to be ignored? There is no ordering between records until you define an order. My question is how do you determine first 3 records?
0
 
sleimanAuthor Commented:
They are manually updated.
0
 
Anthony PerkinsCommented:
sleiman,

This question is going no where very fast.  Please take the time to gather your thoughts and try and present a coherent question.  Preferably supply a script that will load data and show how the data should be presented.  If you are unable to do that, at the very least post the structure of your table and some sample data.

Try and help us help you.
0
 
sleimanAuthor Commented:
Hi everyone, thanks for helping out with this. I will provide more details hoping it will help:

My table has two columns:
Make, OrderNumber

Sample Data:
Lexus  1
Honda 2
Toyota 3
Acura 4
Audi 5

the script in my initial question is being use to renumber the order number and it id designed to allow me to specify where to start sorting. So, in the example above, I want Lexus, Honda & Toyota to be 1,2,3. and I want to script to sort everything after 3 in alpha order using the Make column. I manually set the ordernumber to 1,2,3. Then I run the script by modifying the @startingnumber to 3. The result of the update script will leave lexus, Honda, Toyota as 1,2,3 and will set Acura, Audi to 4,5 and so on if there are more entries. In some cases, I may have 1-5 or 1-10 manually set and then the script adjusted to start sorting at the desired number. the issue is that sometimes, the sort does not seem to put the makes in the right order based on the alpha sort. For example, Lancia comes after Landrover and should come before it. that is the issue that I am having and I am looking for a fix or a better more reliable way to do this.

Hope this helps. let me know if I can add anything else.

0
 
Anthony PerkinsCommented:
Perhaps something like this (but quite frankly I am not following you at all):
SELECT	*
FROM	YourTable		
ORDER BY
	CASE 
		WHEN OrderNumber <= 3 THEN STR(OrderNumber, 20)
		ELSE Make
	END

Open in new window

0
 
Anthony PerkinsCommented:
This is how I tested it and I just wish you had supplied us with some SQL Scripts, instead of having to do it ourselves:
DECLARE @YourTable TABLE (
	Make varchar(20) NOT NULL,
	OrderNumber integer NOT NULL
	)

SET NOCOUNT ON
INSERT	@YourTable(Make, OrderNumber) 
VALUES	('Lexus', 1),
	('Honda', 2),
	('Toyota', 3),
	('Acura', 4),
	('Audi', 5),
	('Landrover', 6),
	('Lancia', 7)

SELECT	*
FROM	@YourTable		
ORDER BY
	CASE 
		WHEN OrderNumber <= 3 THEN STR(OrderNumber, 20)
		ELSE Make
	END

Open in new window

0
 
sleimanAuthor Commented:
thanks acperkins, this looks like it will work. it gave me the correct list with the first 3 items having ordernumber (1,2,3) and the remaining list was ordered correctly.

How do I use this script instead of my original script to update the ordernumber column to the new value?

I am also confused to what i can provide you to explain what I am trying to do. It seems though that your answer should work (now just need the update script). Please let me know what is confusing.
0
 
Anthony PerkinsCommented:
Try it this way:
UPDATE	t
SET OrderNumber = NewOrderNumber
FROM YourTable t
		INNER JOIN (
		SELECT	OrderNumber,
				ROW_NUMBER() OVER (ORDER BY CASE WHEN OrderNumber <= 3 THEN STR(OrderNumber, 20) ELSE Make END) NewOrderNumber
		FROM	YourTable) d ON t.OrderNumber = d.OrderNumber

Open in new window

0
 
Anthony PerkinsCommented:
This is how I tested it:
DECLARE @YourTable TABLE (
	Make varchar(20) NOT NULL,
	OrderNumber integer NOT NULL
	)

SET NOCOUNT ON
INSERT	@YourTable(Make, OrderNumber) 
VALUES	('Lexus', 1),
	('Honda', 2),
	('Toyota', 3),
	('Acura', 4),
	('Audi', 5),
	('Landrover', 6),
	('Lancia', 7)

UPDATE	t
SET	OrderNumber = NewOrderNumber
FROM	@YourTable t
	INNER JOIN (
		SELECT	OrderNumber,
			ROW_NUMBER() OVER (ORDER BY CASE WHEN OrderNumber <= 3 THEN STR(OrderNumber, 20) ELSE Make END) NewOrderNumber
		FROM	@YourTable) d ON t.OrderNumber = d.OrderNumber

SELECT	*
FROM	@YourTable
ORDER BY
	OrderNumber

Open in new window

0
 
sleimanAuthor Commented:
getting an error on Row_number()

'ROW_NUMBER' is not a recognized function

what am I missing?
0
 
SharathData EngineerCommented:
What is your database version?
0
 
sleimanAuthor Commented:
Sql 2000
0
 
Anthony PerkinsCommented:
Then you cannot use ROW_NUMBER()  You will have to insert into a temporary table and then use that to renumber your table.
0
 
sleimanAuthor Commented:
would you mind showing me the script to do that. I would have to insert into a temp table, delete all entries from main table and then insert into main table from temp table.
0
 
Anthony PerkinsCommented:
Something like this:
DECLARE @NewOrderNumbers TABLE (
	OrderNumber integer NOT NULL PRIMARY KEY,
	NewOrderNumber integer NOT NULL IDENTITY(1, 1)
	)

INSERT	@NewOrderNumbers (OrderNumber)
SELECT	OrderNumber
FROM	@YourTable		
ORDER BY
	CASE 
		WHEN OrderNumber <= 3 THEN STR(OrderNumber, 20)
		ELSE Make
	END 


UPDATE	t
SET	OrderNumber = d.NewOrderNumber
FROM	YourTable t
	INNER JOIN @NewOrderNumbers d ON t.OrderNumber = d.OrderNumber

SELECT	*
FROM	YourTable
ORDER BY
	OrderNumber

Open in new window

0
 
Anthony PerkinsCommented:
This is how I tested it:
DECLARE @YourTable TABLE (
	Make varchar(20) NOT NULL,
	OrderNumber integer NOT NULL
	)

SET NOCOUNT ON
INSERT	@YourTable(Make, OrderNumber) 
VALUES	('Lexus', 1),
	('Honda', 2),
	('Toyota', 3),
	('Acura', 4),
	('Audi', 5),
	('Landrover', 6),
	('Lancia', 7)

DECLARE @NewOrderNumbers TABLE (
	OrderNumber integer NOT NULL PRIMARY KEY,
	NewOrderNumber integer NOT NULL IDENTITY(1, 1)
	)

INSERT	@NewOrderNumbers (OrderNumber)
SELECT	OrderNumber
FROM	@YourTable		
ORDER BY
	CASE 
		WHEN OrderNumber <= 3 THEN STR(OrderNumber, 20)
		ELSE Make
	END 


UPDATE	t
SET	OrderNumber = d.NewOrderNumber
FROM	@YourTable t
	INNER JOIN @NewOrderNumbers d ON t.OrderNumber = d.OrderNumber

SELECT	*
FROM	@YourTable
ORDER BY
	OrderNumber

Open in new window

0
 
sleimanAuthor Commented:
any chance you can examine my script and tell me why it was not working? Why was the sort not sorting properly?
0
 
Anthony PerkinsCommented:
I can see a couple of reasons:

DECLARE @StartingNumber integer
set @startingnumber = 6

update t
set t.ordernumber = (select count(1) from MakeTable t2 where t2.Make <= t.Make And t2.OrderNumber > 7) + @startingnumber
From makesandmodels t
 WHERE t.OrderNumber > 7
0
 
sleimanAuthor Commented:
thanks acperkins, that still returns the wrong sort where Lancia is after landrover for example. it it just bugging me to why it would not sort correctly.
0
 
Anthony PerkinsCommented:
It returned the correct results for me, but then I am not using your real data.

This is how I tested it:
DECLARE @YourTable TABLE (
	Make varchar(20) NOT NULL,
	OrderNumber integer NOT NULL
	)

SET NOCOUNT ON
INSERT	@YourTable(Make, OrderNumber) 
VALUES	('Lexus', 1),
	('Honda', 2),
	('Toyota', 3),
	('Acura', 4),
	('Audi', 5),
	('Landrover', 6),
	('Lancia', 7)

DECLARE @StartingNumber integer
set @startingnumber = 3

UPDATE  t
SET     t.ordernumber = (SELECT COUNT(1)
                         FROM   @YourTable t2
                         WHERE  t2.Make <= t.Make
			AND t2.OrderNumber > 3
                        ) + @startingnumber
FROM    @YourTable t
WHERE   t.OrderNumber > 3

SELECT *
FROM @YourTable
ORDER BY
	OrderNumber

Output:
Make	OrderNumber
Lexus	1
Honda	2
Toyota	3
Acura	4
Audi	5
Lancia	6
Landrover	7

Open in new window

0
 
sleimanAuthor Commented:
OK. I will examine my data and try again.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 15
  • 10
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now