How to stop GROUP BY from messing up 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

Transact-SQL (2000)

How to stop GROUP BY from messing up order?

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-02-05 : 13:42:22

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-07 : 08:25:29

this item was cross-posted......(and answered)....also in the developer forum.....should now be deleted.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-07 : 08:44:38

Here's the link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12645

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources