Query Format

[DarkConsultant]

Hi All,

I'm trying to build a query but getting nowhere ....

Example Table
UIN group_name subgroup_of etc ...
1 Group1 NULL
2 Group2 NULL
3 SubGroup1 2
4 SubGroup2 1
5 SubGroup3 1

I want the results to be ...
1 Group1 NULL
4 SubGroup2 1
5 SubGroup3 1
3 Group2 NULL
2 SubGroup1 2

but cant find the correct query. Ive tried group by and order by but I either get the groups order correct or the subgroups but not both.

Can anyone help?

Regards


DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[r937]

SELECT UIN 
     , group_name  
     , subgroup_of 
  FROM daTable
ORDER
    BY COALESCE(subgroup_of,UIN)
     , UIN

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Try

select * from GroupsInfo
ORDER BY coalesce(Subgroup_of, UIN), case when Subgroup_of IS NULL then 0 else 1 end, UIN

PluralSight Learning Library

 

[DarkConsultant]

Thanks r937 or can I call you r?

I have never heard of coalesce before (well of course I had heard of the word before but not in SQL and its not mentioned in any of my books) it works perfectly.

Many thanks.

David

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[gbaughma]

COALESCE is a great function. I use it regularly.

COALESCE returns the first "Non-null".

Let's say you have two fields:

Field1 Field2
-----------------
NULL SomeValue

If you do COALESCE(Field1,Field2) you will get "SomeValue"

It's also great if you want a "Default"

COALESCE(Field1,'DefaultValue') will return "DefaultValue"

COALESCE(Field2,'DefaultValue') will return "SomeValue"

... it's ALSO great if you don't want nulls returned, where you may have nulls.

COALESCE(Field1,'') will always make sure you get an empty string (NOT the same as a null)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[DarkConsultant]

Hi Greg,

Thanks for the info I am now a fan and will never forget COALESCE. You have answered many queries with your answer and I now have about a million lines of code to go through to remove my patchs and install the new code. I had my own ways of dealing with NULLs and defaults but they dont compare with the new (to me) function.

Last question ... I ask everyone ... I am using VB.NET 2005 and love it, I have three pro apps on the market which earn me a good living and I take coding jobs from anywhere. I have tried VB.NET 2010 but cannot see enough advantage in upgrading. Do you know differently? Do you even code in VB.NET? Its a bit of a throw away question but every time I ask a search engine it leads to a 'my PC is better than yours' discussion.

Thanks again

David

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[gbaughma]

I am relatively new to ASP.NET. Up until about 10 months ago, I programmed in VB6 and Classic ASP.

I am using Visual Studio Professional 2010; I have found it crucial to what I do.

I have written (or re-written) several applications, such as a Point of Sale system that we use regularly. I also use it to write ASP.NET applications and DotNetNuke modules.

While the IntelliSense for classic ASP and VBScript seems to have disappeared from 2010 (I just use 2005 express for those), I must say that I'm quite happy with 2010 overall.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[DarkConsultant]

Hello,

I dont know how to do this but is anyone still following this thread cos I need more help with the answer.

Table yada
UIN Name SubGroupOf etc ...
1 Group A NULL
2 New 1
3 Bought 1
4 Call Later 1
5 Dead 6
6 Group B NULL
7 Newish 1

2 groups and five subgroups, I want to return the data in the following orders ..
Main Group followed by subgroups in alphabetical order (1,3,4,2,7,6,5) OR main group followed by subgroups in UIN order (1,2,3,4,7,6,5).

SELECT * FROM yada ORDER BY COALESCE(SubGroupOf,UIN),UIN returns 1,2,3,4,7,5,6

Any suggestions please?

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[r937]

Main Group followed by subgroups in alphabetical order (1,3,4,2,7,6,5)

ORDER BY COALESCE(SubGroupOf,UIN),SubGroupOf,name

main group followed by subgroups in UIN order (1,2,3,4,7,6,5)

ORDER BY COALESCE(SubGroupOf,UIN),SubGroupOf,UIN

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DarkConsultant]

Hi Rudy,

In the white heat of yesterday I swear I tried that combo (but of course I couldn't have).

If your book is available for Kindle I will buy a copy, let me know eh?

Kindest regards

David

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[DarkConsultant]

Hi Again Rudy,

Found book, purchased and am reading it now ....

Thanks again.

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[DarkConsultant]

Rudy,

Its a stroke of genius shoehorning a Hitchhiker quote into a SQL book.

The book is very good and now travels everywhere (on Kindle) with me.

Thanks again for the help

David

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[r937]

thanks for the kind words, david

if you have any questions about the book, feel free to contact me directly



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon