SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distinct with Order by problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bro840
Starting Member

5 Posts
Posted - 02/04/2013 :  14:34:39  Show Profile  Reply with Quote
Hi

I'm getting a little disturbed with a query, which one i'm not really able to handle.

I' want to select all distinct JORNADAS from JOGOS:

SELECT DISTINCT (JOGOS.JORNADA) FROM JOGOS

But i need to sort this query by the JOGOS.ID, and in order to this being possible i need to add JOGOS.ID to the SELECT clause.

SELECT DISTINCT (JOGOS.JORNADA), JOGOS.ID FROM JOGOS ORDER BY JOGOS.ID

The problem is that i'm getting duplicated data on JOGOS.JORNADA column

Is possible accomplish what i want?

Thank you

TG
Flowing Fount of Yak Knowledge

USA
5500 Posts
Posted - 02/04/2013 :  14:41:45  Show Profile  Reply with Quote
sounds like a given JORNADA has multiple ID values. Do you care which one is returned? If not perhaps switch from distinct to GROUP BY and aggregate the ID with something like min or max.

Be One with the Optimizer
TG
Go to Top of Page

bro840
Starting Member

5 Posts
Posted - 02/04/2013 :  15:05:31  Show Profile  Reply with Quote
Thank you TG for your reply

Indeed, for a given JORNADA there are multiples ID's.

I don't know if i realized your suggestion but i will try do some experiences based on what you said
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5500 Posts
Posted - 02/04/2013 :  15:20:19  Show Profile  Reply with Quote
What I mean is this:
select jornada from jogos group by jornada order by min(id)

now it will be sorted by the minimum id for each jornada.

Be One with the Optimizer
TG
Go to Top of Page

bro840
Starting Member

5 Posts
Posted - 02/04/2013 :  15:26:04  Show Profile  Reply with Quote
Finally someone who knows what is talking about!!!

Just perfect! Can you teach me the background logic please?

Very nice ;)
Go to Top of Page

bro840
Starting Member

5 Posts
Posted - 02/04/2013 :  15:29:47  Show Profile  Reply with Quote
I think i get it ohh so beautifully simple
Go to Top of Page

bro840
Starting Member

5 Posts
Posted - 02/04/2013 :  15:36:06  Show Profile  Reply with Quote
So here: Select jornada from jogos -> I will get all the data from this column
And here: Group by jornada -> I will group all the common data in one simple record.
And here: Order by min(id) -> I'm saying for it showing all the agregated groups by min ID

It is like that?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5500 Posts
Posted - 02/04/2013 :  15:50:24  Show Profile  Reply with Quote
you got it.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000