Solved

SQL Sorting

Posted on 2011-02-17
34
281 Views
Last Modified: 2012-05-11
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
Comment
Question by:sleiman
  • 15
  • 10
  • 7
  • +2
34 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34921711
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34922361
Can you provide some sample data with expected result?
0
 

Author Comment

by:sleiman
ID: 34922510
Lexus  1
Honda 2
Toyota 3
Acuara 4
Audi 5
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34922513
Are you fine with OrderNumber? Those are not in alphabetical order. Also what is the expected result?
0
 

Author Comment

by:sleiman
ID: 34922528
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
 

Author Comment

by:sleiman
ID: 34922543
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34923431
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
 

Expert Comment

by:RyanSandberg
ID: 34924001
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
 

Author Comment

by:sleiman
ID: 34927045
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34930606
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
 

Author Comment

by:sleiman
ID: 34930869
I need it to update the table with the new order.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34931158
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
 

Author Comment

by:sleiman
ID: 34931261
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34931264
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
 

Author Comment

by:sleiman
ID: 34931274
They are manually updated.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34931352
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
 

Author Comment

by:sleiman
ID: 34931710
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934107
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934114
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
 

Author Comment

by:sleiman
ID: 34934229
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934420
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934424
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
 

Author Comment

by:sleiman
ID: 34934439
getting an error on Row_number()

'ROW_NUMBER' is not a recognized function

what am I missing?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34934879
What is your database version?
0
 

Author Comment

by:sleiman
ID: 34935136
Sql 2000
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34935226
Then you cannot use ROW_NUMBER()  You will have to insert into a temporary table and then use that to renumber your table.
0
 

Author Comment

by:sleiman
ID: 34935462
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34935563
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34935565
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
 

Author Closing Comment

by:sleiman
ID: 34935847
any chance you can examine my script and tell me why it was not working? Why was the sort not sorting properly?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34939114
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
 

Author Comment

by:sleiman
ID: 34940561
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34942840
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
 

Author Comment

by:sleiman
ID: 34944400
OK. I will examine my data and try again.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now