Can I Stop GROUP BY from messing up my order? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Can I Stop GROUP BY from messing up my order?

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-02-05 : 13:41:21

Hiya!
I have this query:
"SELECT Voucher,Printcode,Description FROM (SELECT d1.Voucher,d1.Diag1 FROM (SELECT Voucher,diag1 FROM VouchersDetail GROUP BY Voucher,Diag1
UNION ALL SELECT Voucher,Diag2 FROM VouchersDetail WHERE Diag2 IS NOT NULL GROUP BY Voucher,Diag2 UNION ALL SELECT Voucher,Diag3 FROM Vouchersdetail WHERE
Diag3 IS NOT NULL GROUP BY Voucher,Diag3 UNION ALL SELECT Voucher,Diag4 FROM VouchersDetail WHERE Diag4 IS NOT NULL GROUP BY Voucher,Diag4)d1 INNER JOIN
(SELECT Voucher FROM VouchersDetail)d2 ON d1.voucher = d2.voucher)d3 INNER JOIN ICDCodes ON d3.diag1 = ICDCodes.Code GROUP BY Voucher,PrintCode,Diagnosis ORDER BY Voucher OPTION (HASH GROUP, CONCAT UNION,FORCE ORDER)"
which goes into a temp table. A data sample it returns is:

Voucher Printcode Description
----------- --------- ------------------------------------------------
20 1224 hi
20 1224 hi
20 123.4 HEADACHE
20 123.4 HEADACHE
30 285.1 ACUTE ANEMIA
30 285.1 ACUTE ANEMIA
30 285.1 ACUTE ANEMIA
30 390.6 PERICARDITIS
30 390.6 PERICARDITIS
30 390.6 PERICARDITIS
30 038.40 SEPSIS
30 038.40 SEPSIS
30 038.40 SEPSIS
30 250.2 DIABETES MELLITUS
30 250.2 DIABETES MELLITUS
30 250.2 DIABETES MELLITUS
30 1224 hi
30 1224 hi
30 1224 hi
I want to eliminate duplicates, but still keep the existing order, so e.g. voucher 30, diag. 390.6 appears as the second diagnosis for voucher 30. GROUP BY messes it up, even though OPTION forces a HASH, and DISTINCT in the outermost SELECT seems to work like an OPTION ORDER GROUP.

Thanks!

Sarah Berger MCSD

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-05 : 13:59:17

You don't have an order by clause and so can't guarantee the order in which data will be returned.
Even if you find a method which works at the moment it may change at any time due to data distribution - or just that the server has decided to process in a different way that time (it's unlikely but could happen).

(Unless you're using v6.5 - which did sort in order of group by fields)

Sorry just reformatted your query to look at what it's doing and spotted an order by clause.
I guess what you're saying is that it doesn't retain the order withing the voucher - so my comment above is still valid - you will have to include a sort field and order on that.

SELECT Voucher,Printcode,Description
FROM
(SELECT d1.Voucher,d1.Diag1
FROM
(SELECT Voucher,diag1 FROM VouchersDetail GROUP BY Voucher,Diag1
UNION ALL
SELECT Voucher,Diag2 FROM VouchersDetail WHERE Diag2 IS NOT NULL GROUP BY Voucher,Diag2 UNION ALL
SELECT Voucher,Diag3 FROM Vouchersdetail WHERE Diag3 IS NOT NULL GROUP BY Voucher,Diag3 UNION ALL
SELECT Voucher,Diag4 FROM VouchersDetail WHERE Diag4 IS NOT NULL GROUP BY Voucher,Diag4
)d1
INNER JOIN
(SELECT Voucher FROM VouchersDetail)d2 ON d1.voucher = d2.voucher)d3
INNER JOIN
ICDCodes ON d3.diag1 = ICDCodes.Code
GROUP BY Voucher,PrintCode,Diagnosis
ORDER BY Voucher
OPTION (HASH GROUP, CONCAT UNION,FORCE ORDER)"

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/05/2002 14:00:33

Edited by - nr on 02/05/2002 14:05:46

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-02-05 : 14:23:14

You can use a temp table to preserve the sort order:


set nocount on

create table #final (
   RowID int identity(1,1) NOT NULL Primary Key Clustered
   , Voucher int
   , PrintCode varchar(20)
   , Description varchar(255)
)

/**
-- your SELECT statement goes below the INSERT statement
insert #final (Voucher, PrintCode, Description)
SELECT Voucher,Printcode,Description FROM (SELECT d1.Voucher.......
**/

-- manually insert data for testing
insert #final (Voucher, PrintCode, Description) values (20 , '1224' ,'hi')
insert #final (Voucher, PrintCode, Description) values (20 , '1224' ,'hi')
insert #final (Voucher, PrintCode, Description) values (20 , '123.4' ,'HEADACHE')
insert #final (Voucher, PrintCode, Description) values (20 , '123.4' ,'HEADACHE')
insert #final (Voucher, PrintCode, Description) values (30 , '285.1' ,'ACUTE ANEMIA')
insert #final (Voucher, PrintCode, Description) values (30 , '285.1' ,'ACUTE ANEMIA')
insert #final (Voucher, PrintCode, Description) values (30 , '285.1' ,'ACUTE ANEMIA')
insert #final (Voucher, PrintCode, Description) values (30 , '1224' ,'hi')
insert #final (Voucher, PrintCode, Description) values (30 , '1224' ,'hi')
insert #final (Voucher, PrintCode, Description) values (30 , '1224' ,'hi')


-- show all records in the table
select * from #final


-- use distinct to get rid of dupes - THIS CHANGES THE SORT ORDER
select distinct /*RowID,*/ Voucher, PrintCode, Description
from #final


-- this syntax preserves the sort order
select /*Min(RowID) RowID,*/ Voucher, PrintCode, Description
from #final
group by Voucher, PrintCode, Description
order by min(RowID)


drop table #final

Here is the output


RowID       Voucher     PrintCode            Description               
----------- ----------- -------------------- ------------------------- 
1           20          1224                 hi
2           20          1224                 hi
3           20          123.4                HEADACHE
4           20          123.4                HEADACHE
5           30          285.1                ACUTE ANEMIA
6           30          285.1                ACUTE ANEMIA
7           30          285.1                ACUTE ANEMIA
8           30          1224                 hi
9           30          1224                 hi
10          30          1224                 hi


Voucher     PrintCode            Description               
----------- -------------------- ------------------------- 
20          1224                 hi
20          123.4                HEADACHE
30          1224                 hi
30          285.1                ACUTE ANEMIA


Voucher     PrintCode            Description               
----------- -------------------- ------------------------- 
20          1224                 hi
20          123.4                HEADACHE
30          285.1                ACUTE ANEMIA
30          1224                 hi

Edited by - muffinman on 02/05/2002 14:24:22

Edited by - muffinman on 02/05/2002 14:26:23

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-05 : 14:27:02

What guarantees the order of the insert into the temp table.
And I've had occasions where the identity doesn't follow the order by clause on the insert - well 1 occassion and with v6.5, had to put into a temp table, put a clustered index on it and insert from there.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-02-05 : 14:44:21

Hiya!
Sorry, my post might have been off. Without the GROUP BY, I got the resultset I posted. With the GROUP BY, it returns this:
Voucher Printcode Descriptio
----------- --------- -----------------------------------------------
20 1224 hi
20 123.4 HEADACHE
30 038.40 SEPSIS
30 1224 hi
30 250.2 DIABETES MELLITUS
30 285.1 ACUTE ANEMIA
30 390.6 PERICARDITIS
which is in messed up order. Distinct returns same. Also, since this is already a lengthy query which dumps into a temp table, another temp table to process it is really way out!

Sarah Berger MCSD

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-02-05 : 14:55:28

As you point out nr, without a fully qualified WHERE clause, the user has no guarantee that the data will sort the same way each time.

(I have never had a problem with Identity values, so I cannot comment on that.)

In looking at the user's SELECT statement, it seems to me that instead of a single SELECT with multiple UNION's, why not just do multiple SELECT's to insert data into the temp table. That way the user will be assured that if she puts the data in the temp table in a defined order, the DISTINCT data will be returned the same way everytime by using the "ORDER BY MIN(RowID)" clause.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-05 : 15:04:43

I guess you want

20 1224 hi
20 123.4 HEADACHE
30 285.1 ACUTE ANEMIA
30 390.6 PERICARDITIS
30 038.40 SEPSIS
30 250.2 DIABETES MELLITUS
30 1224 hi

The question really is what makes this the correct order.
You have nothing in the query to order by anything other than Voucher so the server is rightly delivering records within voucher in any order.
To repeat - if you want these records in a specific order you will need to specify an ordering field.
Note that the order in which they were added is meaningless in a relational database without a field to determine it.

Don't know what the ordering criteria is but something like
(SELECT Voucher,diag1, sortorder = 1 FROM VouchersDetailGROUP BY Voucher,Diag1
UNION ALL
SELECT Voucher,Diag2,2 FROM VouchersDetail
...
)
order by voucher, sortorder

Or if you have a min date or ID for the entry just sort on that.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-02-05 : 15:41:46

Hiya!
Thanks a lot, guys! I did a little spinoff on your idea, MuffinMan, and since I'm anyway using a temp table, I just added the rowid column there and changed my cursor's "select into variable" statement to select the rowid column, which I then ignore.

quote:
select /*Min(RowID) RowID,*/ Voucher, PrintCode, Descriptionfrom #finalgroup by Voucher, PrintCode, Descriptionorder by min(RowID)

This works and preserves the right sort order.
nr, you're right about there not really being a sort column, but I'm not quite sure yet if I want one there except for Voucher. Believe me, you don't want to hear the explanation. It can give anyone a nervous breakdown.

Thanks a million for your help!! And HOORAY for SQLTEAM!

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources