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
 General SQL Server Forums
 New to SQL Server Programming
 Distinct with Order by problem

Author  Topic 

bro840
Starting Member

5 Posts

Posted - 2013-02-04 : 14:34:39
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-04 : 14:41:45
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 - 2013-02-04 : 15:05:31
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-04 : 15:20:19
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 - 2013-02-04 : 15:26:04
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 - 2013-02-04 : 15:29:47
I think i get it ohh so beautifully simple
Go to Top of Page

bro840
Starting Member

5 Posts

Posted - 2013-02-04 : 15:36:06
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-04 : 15:50:24
you got it.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -