Special order in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Special order in a query

Author

Topic

jorekami
Starting Member

4 Posts

Posted - 2008-10-27 : 12:32:01

Hi, I'm from Spain, so excuse me for my English.

I have a table and I need order the registrys with this logic:

There are two fields One is "MantID" and the other is "ID". Both fields are Identifier Unique Global

MantID have information about field ID from this table

---------- ID MantID
Registry1: 112 NULL
Registry2: 475 NULL
Registry3: 365 112
Registry4: 25 475
Registry5: 347 112

So I want that the query (SELECT * FROM mytable) return the registries there:

Registry1
Registry3 MantID is ID from Registry1
Registry5 MantID is ID from Registry1
Registry2
Registry4 MantID is ID from Registry2

I need order so firs show a registry with MantID=Null, next all registries that MantID is the same ID from the before registry. After other registry with MantID=Null, next all registries with MantID=ID

Is this order possible?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:45:56

[code]SELECT *,CASE WHEN MantID IS NULL THEN 0 ELSE 1 END AS OrdVal FROM mytable ORDER BY COALESCE(MantID,ID),OrdVal[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 12:57:30

Is it a possibility that you can have several levels of MantID?
For example

----------  ID  MantID
Registry1: 112   NULL
Registry2: 475   NULL
Registry3: 365    112
Registry4:  25    475
Registry5: 347    112
Registry6: 999    347

E 12°55'05.63"
N 56°04'39.26"

jorekami
Starting Member

4 Posts

Posted - 2008-10-28 : 04:42:40

Hi again

Peso: No, it isn't possible have several levels of MantID

visakh16: Your query is very good, but doesn't order exactly such as I need. The result is like that:

---------- ID MantID
Registry1: 112 NULL
Registry3: 365 112
Registry5: 347 112
Registry4: 25 475
Registry2: 475 NULL

Thank again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 04:53:38

why Registry2 came after Registry4 even when MantID is NULL for it? can you explain that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 05:02:43

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
ID INT,
MantID INT
)

INSERT @Sample
SELECT 2, 475, NULL UNION ALL
SELECT 5, 347, 112 UNION ALL
SELECT 4, 25, 475 UNION ALL
SELECT 3, 365, 112 UNION ALL
SELECT 1, 112, NULL

-- Peso
SELECT *
FROM @Sample
ORDER BY COALESCE(MantID, ID),
MantID,
ID

-- Visakh16
SELECT *,
CASE
WHEN MantID IS NULL THEN 0
ELSE 1
END AS OrdVal
FROM @Sample
ORDER BY COALESCE(MantID, ID),
OrdVal[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 05:06:47

I believe order should be

---------- ID MantID
Registry1: 112 NULL
Registry5: 347 112
Registry3: 365 112
Registry2: 475 NULL
Registry4: 25 475

to be consequent.
Parent records are all those with MantID NULL. For these records you sort by ID.
For all child records belonging to a parent record, you "insert" these after the MantID value.
But these records should also be sorted by ID to be consequent, right?

E 12°55'05.63"
N 56°04'39.26"

jorekami
Starting Member

4 Posts

Posted - 2008-10-28 : 05:57:13

Yes, it's all right such as you explain, Peso. in my example I wrote bad the order. Excuse me...

The code work ok, but in my case, I can't use auxiliar tables (declare, insert), I only can use the data that I tell you. Really I think that it's impossible order such as peso explain, if don't use declare, inserts, etc.

Thanks again, you are very good!

jorekami
Starting Member

4 Posts

Posted - 2008-10-28 : 06:23:10

Excuse me again, I probe with my data (without using inserts, declare, etc) and work fine both solutions

Thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 06:24:20

Peso was never suggesting you to create table. the declare and insert are just to create some sample data. you need to do only last part i.e select statement which retrives the rows from your table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources